• Excel Book Excerpt

Excel Join Text with a Date or Currency

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Join Text with a Date
or Currency

Problem: I just learned about concatenation, and I’m trying to join text with currency and with a date. As you can see in cell B13, when I attempt to join both date and currency with text, the currency loses the dollar sign and the date appears as a strange number. What am I doing wrong?

LE10000196.jpg

Figure 225 The formula in B13 fails miserably.

Strategy: Excel internally stores dates as numbers and relies on the number format to display the number as a date. In the formula, you can use the TEXT function to convert a date or a number into text with a particular numeric format. For example, the formula =TEXT(F1+F3,"mm/dd/yyyy") would produce the text 07/18/2014. Thanks to the variety of custom number formats, you could also use =TEXT(F1+F3,"dddd, mmmm d, yyyy") to create the text Friday, July 18, 2014.

Additional Details: If you are not sure of the actual custom number format codes, you can query them from any existing cell. Here’s an example:

1. Select cell E11.

2. Press Ctrl+1 to display the Format Cells dialog.

3. Click the Number tab and then select the Custom category. Excel will show the actual code used to generate the format in that cell.

4. Highlight those characters, press Ctrl+C to copy to the Clipboard, and paste into the TEXT function.

5. Change the formula in B13 to

=“Please remit “&TEXT(E11,"$#,##0.00")&

" before “&TEXT(F1+F3,"dddd, mmmm d, yyyy")

LE10000197.jpg

Figure 226 Use the TEXT function to format dates and currency.

Additional Details: Excel stores dates as the number of days elapsed since January 1, 1900 (on a PC), or since January 1, 1904 (on a Mac). The 41838 shown in cell B13 of Figure 225 corresponds to July 18, 2014. While this is a fascinating bit of information (if you are Cliff Claven), I’ve never had a manager call and ask, “Hey, how many days after January 1, 1900, is that receivable due?" This method makes it easy for Excel to calculate differences between two dates.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: