To format a date, use “m” for the month, “d” for the day, and “y” for the year, or you can spell out the month names with “mmmm.” “=TEXT(41640,“m/d/y”) will appear as “1/1/14,” and “=TEXT(41640,“mmmm d, yyyy”) will appear as “January 1, 2014.”.
Each # is a number that can be displayed if there is a digit, but will be left out if there is no digit. Each 0 is a number that must be displayed, even if it is zero.
While a thorough discussion of TEXT is a discussion for another post, you should be aware of two format codes that you’ll want to use with the CONCATENATE function: The solution – Use an additional function, TEXT, which converts a number to text using a specified format. The CONCATENATE function knows nothing about date formats, however, so it displays the date in Excel’s internal date-time code. Why it happens – Excel stores all dates as numbers internally, displaying them as dates to make it easier for humans to read. Nevertheless, the formula results in “Beginning 41640 and ending 42004.” The two dates and turned into numbers, 4164. Note the spaces and the conjunction “and” added between the variables. With decimals or with dates, however, the result might be unintelligible. If you are joining the number 1047 and the string “Maple Street,” the formula “=CONCATENATE(1047,“ ”,“Maple Street”)” works just fine, producing “1047 Maple Street.” The CONCATENATE function converts numbers to text.
You must do it by inserting “ ” between the two strings, as shown above, or by replacing that space with a hyphen or other punctuation. For example, if you have the customer’s first name in Column A and the last name in Column B, you could use “=CONCATENATE(A3,“ ”,B3)” to produce a string containing first name and last name.īe aware that when you join two strings, Excel does not insert a space or any punctuation between the two. The CONCATENATE function joins two or more text strings together into one string.