#Mail merge from excel to word 2010 how to
#Mail merge from excel to word 2010 code

But let’s say that you can’t or don’t want to change your Excel spreadsheet. For example, instead of displaying 1.0014, Mail Merge showed 1.0013. In rare cases, the ROUND function causes Mail Merge to show a slightly different number. For example, a Mail Merge document occasionally shows 4 decimal places when the ROUND function had specified 2 or 3. The ROUND function sometimes causes Mail Merge to display the wrong number of decimal places.The ROUND function will truncate trailing zeros.The ROUND function will not preserve the dollar sign or the thousands separator (comma).In many cases, the ROUND() function will work well with Mail Merge, but you may want to steer clear of it because of the following reasons: The advantage is that the rounded number is not text and can still be used for further calculations. As its name implies, Excel’s ROUND() function will permanently round a number up or down to the number of decimal places you specify. Instead of the TEXT() function, some users prefer to use the ROUND() function to trim off extra decimal places. If you need to convert them into numbers again, just use the VALUE function. Just remember that these cells may look like they contain numbers, but they cannot be used in SUM or AVERAGE functions. Supplementing a spreadsheet table with a few columns featuring TEXT functions is a simple and direct approach. Also, Excel has an additional code, “MMMMM”, which returns a single letter abbreviation for the month (e.g., “J, F, M, A, …”).

But Word is not so smart, and you must use the capitalized letter to refer to months. In almost all cases, Excel is smart enough to figure out which units you are talking about. In Excel, you can use either “M” or “m” for months or minutes. Unfortunately, there is a slight difference between the way these codes work in Excel and Word in Microsoft Office 2013. As an extra bonus, they can also be used in the “Numeric Switches” in Word Mail-Merge Fields described in the next section. These codes for numbers, dates, and times can be used in the TEXT function or in Excel’s Custom number formats.

#Mail merge from excel to word 2010 code

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.

#Mail merge from excel to word 2010 how to

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.