Solved

Replace Formatted Numbers with text

Posted on 2016-08-10
9
50 Views
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!

-Todd
0
Comment
Question by:thutchinson
  • 5
  • 3
9 Comments
 
LVL 5

Expert Comment

by:Manuel Flores
Comment Utility
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
 

Author Comment

by:thutchinson
Comment Utility
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
 
LVL 5

Expert Comment

by:Manuel Flores
Comment Utility
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
 
LVL 5

Accepted Solution

by:
Manuel Flores earned 500 total points
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 5

Expert Comment

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

TEXT function
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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
 

Author Closing Comment

by:thutchinson
Comment Utility
Thank you, Manuel.
0
 
LVL 5

Expert Comment

by:Manuel Flores
Comment Utility
thxs
0
 

Author Comment

by:thutchinson
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now