Replace Formatted Numbers with text

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!

-Todd
thutchinsonAsked:
Who is Participating?
 
Manuel FloresConnect With a Mentor Commented:
I think is this:

TEXT function;
Converts a value to text in a specific number format.
Syntax
TEXT(value;format_text)
▪      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).
0
 
Manuel FloresCommented:
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.

..MFlores..
0
 
thutchinsonAuthor Commented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Manuel FloresCommented:
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.
0
 
Manuel FloresCommented:
Apart from my local currency and dot-comma numbering system;

TEXT function
0
 
GrahamSkanRetiredCommented:
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.
0
 
thutchinsonAuthor Commented:
Thank you, Manuel.
0
 
Manuel FloresCommented:
thxs
0
 
thutchinsonAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.