Forum Discussion
Solved
Shorten large numbers and concatenate while keeping the format
Hi all, I would like to shorten a large number e.g. 100.000 to 100,0K and than concatenate with another cell. Example data: A1 = 100.000 B1 = Reach Here is how far I have come but unfo...
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
Thanks a lot Riny! Unfortunately as soon as I touch the cell in your sheet it turns the result into this 1200000,00,,M Reach
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.
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.