Adding text to numbers using TEXT function

[jelzin]

I'm working on a report where I'm auto-generating a text report based on this months numbers using several cells and IF functions etc. The result is something like this:

Inbound of $24.5M is up $3.8M from last month.
EBIT for the month of ($0.7)M was equally to forecast.
Compared to budget (8.9)M is FX related.

I'm using the TEXT-funtion to format the numbers, currently I'm using:
TEXT(ABS(Numbers!A1),"$0.0;($0.0)") but what I want to is to add M to all numbers directly instead of having to do it with a cell reference to a cell with the value M.

First of all it would make all the formulas a little shorter but the real reason is that it would hopefull allow me to formate the numbers as ($0.5M) instead of ($0.5)M. The differece might seem silly but my company is using the second format and I know that my boss will ask about it.

I know it is possible to do it using custom cell formating: $0.0"M";($0.0"M")
but since the cell contains text as well as numbers I can't custom formate the cell. Is there anyway to add text to the format_text part of the TEXT-function?

I thought that TEXT(A1,$0.0"M";($0.0"M")) might work but unfortunately it doesn't.

 

[snowblizz]

It seems you can only include a single pair of " in the TEXT function to show the format string. That's why it fails.
Interestingly I can make Excel format the cell in the way you want through a custom format, but not through the TEXT function. Copying it and pasting into word keeps the formatting as WYSIWYG.

 

[jelzin]

It seems you can only include a single pair of " in the TEXT function to show the format string. That's why it fails.
Interestingly I can make Excel format the cell in the way you want through a custom format, but not through the TEXT function. Copying it and pasting into word keeps the formatting as WYSIWYG.

Thanks for your fast answer!

True, it seems to be that way. At first I thought that the "s was unnecessary for "$0.0;($0.0)" since they are not required in the custom cell format and you thus could use your only avilable "" around the M but I assume that's the reason you can add text in the custom format but not using the TEXT-function.

I do paste into word as it is (using Links - LINK Excel.Sheet.8"Link") but only the finished sentence from a single cell. I guess I could paste single cells and build the sentences in word instead of excel but since the links tend to break and create problems I try to keep them at a minimum.

Pherhaps the best solution is to include another IF-formula that controls whether the numbers is negative (the majority should be positive) and build those differently i.e. not using the text_format but cell references instead.

 

[snowblizz]

Using an if might be the answer.

This seems to work :

=IF(A1>0;CONCATENATE(TEXT(A1;"$0,0");"M"); CONCATENATE("(";TEXT(A1;";$0,0");"M)"))

you'll need to change the , and ; to whatever you use though. , = decimal ; = formula separator

 

[pgc01]

Hi

Try:

=TEXT(A1,"$0.0""M"";($0.0""M"")")

 

[jelzin]

Great, works perfectly! Thanks for all help snowblizz & pgc!

/jelzin