Merge Custom Format $14.9M to Word?

[sammipd]

I have a column of dollar amounts in the millions. Using Custom Format $14,920,569 now appears as $14.9M. However, when I merge to a Word doc the formatting disappears and just the eight numbers appear. How can I make this work?

Custom Format
[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

Thank you!

 

[Joe4]

The Excel Custom Formatting only applies to what is viewed in Excel. It does not change the underlying value, and therefore the Custom Formatting is not sent to Word in your mail merge.
You either need to change the value to a Text field in Excel (you can use the TEXT function to apply custom formats to values and convert to text), or apply Custom Formatting in the Word merge field.
See: https://www.extendoffice.com/docume...il-merge-date-currency-and-number-format.html

 

[CalcSux78]

Exactly what @Joe4 said...

You could run a macro to convert each cell to a value (to remove formulas) then convert to text with something like this:

Sub PrepForExport()
Dim cel As Range


For Each cel In ActiveSheet.UsedRange.Cells
    cel.Value = cel.Value 'convert formulas to values
Next cel
'convert to text to preserve formatting
For Each cel In ActiveSheet.UsedRange.Cells
    cel.Value = cel.Text
Next cel
End Sub

 

[sammipd]

Thank you Joe4. It worked wonderfully!

 

[Joe4]

You are welcome.

 

[Macropod]

There is no need to modify your data source, which is generally undesirable; simply use some field coding in Word to reformat the output. For example:
{IF{MERGEFIELD Value}< 1000000 {={MERGEFIELD Value}/1000 \# "$0.0'K'"} {={MERGEFIELD Value}/1000000 \# "$0.0'M'"}}
or:
{IF«Value»< 1000000 {=«Value»/1000 \# "$0.0'K'"} {=«Value»/1000000 \# "$0.0'M'"}}
where 'Value' is your field's name.

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required.