Replace Formatted Numbers with text

Posted on 2016-08-10
Last Modified: 2016-08-10
We are having problems with a Microsoft Word merge using a large Excel file as a data source.  Using DDE, the format used to transfer with the data.  Now we lose all formatting and can't seem to get DDE to work.

If we format numbers as text such as "$ 19,599.25" then the numbers come over formatted as text.  Does anyone know of any way to change the cells from numeric format  (Say, currency) to text formatted the same?  Can I do this with a macro?

Thanks for your help!

Question by:thutchinson
  • 5
  • 3

Expert Comment

by:Manuel Flores
ID: 41751135
As far as I know, it should be as easy as just format the cells with Text.

Right click -> Format Cell -> Text

Maybe is not what you need, please explain.


Author Comment

ID: 41751175
Hi Manuel,

If you format as text, you lose the visual elements.  So,  $119,259.25 becomes 19259.25

Remember, we need to keep the visual elements because it is being inserted into a letter.

Expert Comment

by:Manuel Flores
ID: 41751191
You could append the $ and even the comma with LENGTH, MID, LEFT and "$" & cell functions.  Is a little tricky but can be done.  Maybe is a quite brute force solution.  

Probably there must be a 'visual' content function to extract a number formatted column as text.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Accepted Solution

Manuel Flores earned 500 total points
ID: 41751197
I think is this:

TEXT function;
Converts a value to text in a specific number format.
▪      value: is a number, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
▪      format_text: is a number format in text form from the Category box on the Number tab in the Format Cells dialog box (not General).

Expert Comment

by:Manuel Flores
ID: 41751204
Apart from my local currency and dot-comma numbering system;

TEXT function
LVL 76

Expert Comment

ID: 41751207
You can format the mailmerge field in the Word merge main document.

Find the field, e.g. <<Amount>>, and right-click on it. Select Toggle Field Codes from the context menu so that it looks like:

{ MERGEFIELD “Amount” }

Insert a field switch so that it looks like:
 { MERGEFIELD “Amount” \# $,#.00}

You can toggle back to the original view (or not). and then try the merge.

Author Closing Comment

ID: 41751230
Thank you, Manuel.

Expert Comment

by:Manuel Flores
ID: 41751232

Author Comment

ID: 41751235
Hi Graham,

Using the switches is the best way for sure. We were having trouble with them until we formatted the Excel source document differently.  Thanks for the help.

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question