Forum Discussion
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 unfortunately it does not work:
=concatenate(text(A1;"[>=999999]#,0.."M";[>=999]#,0."K";0");" ";B1)
The number format needs to stay flexible and the result is supposed to look like this 100,0K Reach
Any help is much appreciated!
Thanks,
Diana
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
6 Replies
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, I ended up with this solution which works perfect!
= TEXT(value;"[>=1000000]#,0.,\M ;[>=1000]#,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.
- 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.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".
