Apr 11 2022 05:00 AM
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
Apr 11 2022 05:31 AM
@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.
Apr 11 2022 05:58 AM
Apr 11 2022 06:00 AM
SolutionLike 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
Apr 11 2022 06:45 AM
Apr 11 2022 06:57 AM - edited Apr 11 2022 07:02 AM
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 "
) &
string
From your previous reply (just received) I think the answer to my question is "yes".
Apr 11 2022 07:06 AM
Apr 11 2022 06:00 AM
SolutionLike 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