Follow
Lucid Gen
  • Marketing
    • Facebook
    • Google
    • Zalo
    • Content marketing
    • Email Marketing
    • Marketing tools
    • SEO
  • Website
    • WordPress
  • Technology
    • Computer
    • Phone
  • Life
No Result
View All Result
Lucid Gen
  • Marketing
    • Facebook
    • Google
    • Zalo
    • Content marketing
    • Email Marketing
    • Marketing tools
    • SEO
  • Website
    • WordPress
  • Technology
    • Computer
    • Phone
  • Life
No Result
View All Result
No Result
View All Result
Lucid Gen

Lucid Gen › Technology › Computer › How to format numbers in Mail Merge exactly

How to format numbers in Mail Merge exactly

Đánh giá bài viết
23/07/2021
0
Cách định dạng số trong Mail Merge cực chuẩn - How to format numbers in Mail Merge exactly

Cách định dạng số trong Mail Merge cực chuẩn - How to format numbers in Mail Merge exactly

449
SHARES
44.8k
VIEWS
Share to Facebook

The magic feature of Mail Merge in the Microsoft Office suite saves users a lot of time and effort. But it’s still not perfect in the format numbers in Mail Merge. If you use a Macbook, Mail Merge will automatically get the correct format in Excel, and Windows will have to add format numbers in Mail Merge manually. I will show you how to do it and the easiest to understand example. Let’s get started!

Related posts

  • How to increase mouse tracking speed Mac
  • How to download Power BI for Mac and Windows
  • How to uninstall apps on Mac completely
Table of contents
  1. How to format numbers in Mail Merge
  2. Mail Merge number format formula table
  3. How to keep Excel’s formatting in Mail Merge
  4. Epilogue

How to format numbers in Mail Merge

Step 1: You right-click on the inserted data and select Toggle Field Codes. Or use the following keyboard shortcut:

  • Windows: Left click on the inserted data and then press Shift F9.
  • macOS: Left click on the inserted data and then press Fn Shift F9.
Open edit format numbers in Mail Merge
Open edit format numbers in Mail Merge

Step 2: You will see the code in the form {MERGEFIELD_ColumnName}. Now you will insert a formula in front of the } to add the format numbers in Mail Merge. Let me give you an example to make it easier to understand; I have data like this in Excel.

Day month YearNumberPercent times 100Percent
February 14, 20215000000.78996.690.9669

Now I insert Numeric data and want it to be in the thousands, round off the decimal to 500,001. I will insert the formula into {MERGEFIELD Number} into {MERGEFIELD Number \# #,##0} .

format numbers in Mail Merge to thousands
format numbers in Mail Merge to thousands with commas

How to format numbers in Mail Merge is as simple as that. Depending on the data type (date, number, money, percentage …), you will choose different formulas. The formula table I have prepared below for you, I compiled according to the common data types in Vietnam.

Video tutorial to format numbers in Mail Merge

Mail Merge number format formula table

FormatRecipeEg
Day month Year\@ dd/MM/yyyyFebruary 14, 2021
Day\@ dd14
Month\@ MM02
Five\@ yyyy2021
Hours and minutes\@ HH:mm17:30
Hours, minutes, and seconds\@ HH:mm:ss17:30:15
Number in thousands\# #,##0500,001
Thousands with decimals (3 digits)\# #,##0.0000500,000.789
Vietnamese currency\# #,##0₫500,001₫
Dollar currency\# $#,##0.00$500,000.79
Percentage (multiplied by 100 in Excel)\# 0%97%
Percentages (multiplied by 100 in Excel) with decimals (2 numbers)\# 0.00%96.69%
Percentages (not multiplied by 100 in Excel){={MERGEFIELD_Percent}*100 # 0%}97%
Percentage (not multiplied by 100 in Excel) with decimal (2 numbers){={MERGEFIELD_Percent}*100 \# 0.00%}96.69%

Applying the above formula table to our example, we will open Toggle Field Codes and insert the formula as shown in this figure.

Add formula to format numbers in Mail Merge
Add formula to format numbers in Mail Merge

For Percentage (not multiplied by 100 in Excel) ie the value in Excel is less than 1 (Example: 0.55), then you add a formula with the multiplication calculation of 100 as shown on the table. Replace Percent_with your percentage column name.

But this way depends on personality. Quite a few people do it right but still get Syntax Error (including me). If you also get the error, the only way to fix it on Excel is to multiply by 100 and then return to Mail Merge using the Percentage formula (multiplied by 100 in Excel) .

How to keep Excel’s formatting in Mail Merge

According to the information of Microsoft and some other source of their learning online, to keep the formatting of Excel in Mail Merge, then in step Confirm Data Source (Confirm Data Source) you check the Show all (Show All ) then select MS Excel Worksheets via DDE (*.xls).

How to keep formatting from Excel in Mail Merge
How to keep formatting from Excel in Mail Merge

But not everyone succeeds this way. I tried it on my friend’s computer and it keeps crashing. This also depends on personality. ^_^

For Office on Mac, everything becomes easier; by default, it has kept the format from Excel in Mail Merge already.

Epilogue

The Microsoft Office has many versions (2003, 2007, 2010, 2016, 2019…,). It is still on different operating systems (Windows, macOS), so no one knows which version will use Excel Worksheets via DDE (* .xls) and the calculation in Toggle Field Codes to format numbers in Mail Merge successfully or not. The rest of the formulas are 100% successful because they are simple.

Leave your comments below the article, good luck!

Đánh giá bài viết
Share179Pin41Share31

Leave a Reply Cancel reply

I will review and reply to all comments within the day. Please feel free to leave your comments on this article!

Your email address will not be published. Required fields are marked *

Recommend for you

  • Trending
  • Latest
Cách cài đặt và sử dụng HTTrack trên Macbook (How to use HTTrack on Mac Terminal)

How to use HTTrack on Mac Terminal

06/08/2021
0
Cách chèn nhạc và video vào PowerPoint nhanh

How to insert video and music to PowerPoint

22/07/2021
0
Cách xuất sản phẩm trên WordPress cho Merchant Center - How to export Google Merchant Center feed from WordPress

How to export Google Merchant Center feed from WordPress

12/07/2021
0
Cách thêm ảnh đại diện Gravatar WordPress - How to use gravatar in WordPress simply

How to use gravatar in WordPress simply

05/08/2021
0
Cách chỉnh tốc độ chuột MacBook, Magic Mouse, Logitech trên MacBook

How to increase mouse tracking speed Mac

14/05/2022
4
Cách cài đặt Power BI trên Mac và Windows | How to download Power BI for Mac and Windows

How to download Power BI for Mac and Windows

15/10/2021
0
Cài đặt tracking chuyển đổi Contact Form 7 How to set up Contact Form 7 tracking

How to set up Contact Form 7 tracking

08/10/2021
0
Cách gỡ ứng dụng trên Mac và dọn dẹp sạch sẽ - How to uninstall apps on Mac completely

How to uninstall apps on Mac completely

08/10/2021
0

Lucid Gen

A blog sharing about digital marketing, miscellaneous about technology and what you might need that the author knows.

Developed by blogger
Minh Khue › Donate

229 Posts and 27 Comments

Disclosure: This website has advertisements. If you don’t have money, how can you write?

 Google Play Microsoft Store

Recent Comments

  • Minh Khuê on How to create an automatic table of contents in Word with headings
  • sam edon on How to create an automatic table of contents in Word with headings
  • Minh Khuê on Facebook image size post and cover
  • Quail on Facebook image size post and cover
  • Minh Khuê on How to increase mouse tracking speed Mac

Image sources

Lucid Gen edits images from the following sources: Freepik, Unsplash & Pixabay.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

  • About
  • Contact
  • Terms and policies

© 2019 Lucid Gen with by Minh Khue DMCAProtected

No Result
View All Result
  • Marketing
    • Facebook
    • Google
    • Zalo
    • Content marketing
    • Email Marketing
    • Marketing tools
    • SEO
  • Website
    • WordPress
  • Technology
    • Computer
    • Phone
  • Life

© 2019 Lucid Gen with by Minh Khue DMCAProtected