Replace Formatted Numbers with text

Posted on 2016-08-10
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Accepted Solution

Manuel Flores earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

752 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