Forum Discussion
Shorten large numbers and concatenate while keeping the format
Like the OP, I tend to find nested quotes tricky to use. In the case of number formatting the backslash "\" acts as an escape character and offers an alternative way of embedding text. The "space" needs neither quotes nor escape; it is recognised as part of the format.
= TEXT( value, "[>=1000000]#,##0.0,,\M ;[>=1000]#,##0.0,\K ;0 " ) & string
DianaGrey Try it this way:
=TEXT(A1,"[>=1000000]#,##0.0,,""M "";[>=1000]#,##0.0,""K "";0")&B1
Though you seem to be using ; as the list separator. Not sure how that affects the formatting string. Attached a file with a working example that should adjust itself to you locale.
If I use it with my data it looks the same.
I have to you the ; as the list separator because it is a German excel sheet.
This is a bit of a long shot but what does the number formatting look like on your computer. Is is defined using english language notation or should the "." decimal and "," thousands separators be switched?
= TEXT( value, "[>=1000000]#.##0,0..\M ; [>=1000]#.##0,0.\K ; 0 " ) & stringFrom your previous reply (just received) I think the answer to my question is "yes".
- It is indeed not the English notation. I need it for German context e.g. 1.000 equals 1K. But the formula I posted above works just fine.