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
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
