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.
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.


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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 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