These codes for numbers, dates, and times can be used in the TEXT function or in Excel’s Custom number formats. Thus, the code “00000” will ensure that the leading zero is not truncated from New Jersey postal zip codes. The “#” placeholder will be replaced by a digit only if it is not a leading or trailing zero. A key thing to remember is that both “0” and “#” act as placeholders for digits, but “0” will force leading or trailing zeros to appear.

Format Codes for Excel’s TEXT FunctionĪ collection of the most commonly used codes is presented at the right. Formatting options such as font, size, and color do not make the trip. The advantage of formatting numbers and dates as text is that text is transported from Excel into a Word Mail Merge document unmolested. In Excel 2016, they can be used in all formulas except aggregate functions, such as, SUM and AVERAGE.) In older versions of Excel, these “numbers” could not be used in calculations.

(Tip: It is a good idea to format the cells containing these formulas in a different style, say, italic, or some unusual color to remind you that these numbers are simply text. Although these codes are identical to those used in the Custom Number formatting feature of Excel, they are rarely seen by most Excel users.įor example, the formulas TEXT(B3, “$#,#0.00”) and TEXT(C5, “M/dd/yy”) convert number and date data into textual data (in this case, “$12,345.67” and “12/01/10”). The downside is that you need to know certain formatting codes. To do this, you need to use Excel’s TEXT function, which enables you to convert a number or date into its equivalent text formatted exactly the way you desire. The first way is to avoid the problem: In the Excel worksheet, insert a column with a formula that converts the Excel numbers or dates into a text format that is exactly the same as what you would want to appear in your Mail Merge document.

For example, a sales result in Excel appears as 100 but in Word it suddenly becomes 99.99999999996!įortunately, there are no less than three different ways to get Mail Merge numbers to appear the way they should. One of the perennial annoyances of Mail Merge is its inability to format numbers from an Excel spreadsheet correctly. Unfortunately, the task of learning to master all of its idiosyncrasies can give you countless headaches. Once you learn how to use it, you can save countless hours of work. It is extremely powerful for creating labels and customized letters, emails, or reports. The Mail Merge feature of Microsoft Word is one of my favorite parts of the program.

Microsoft word file formats