SOLVED

Shorten large numbers and concatenate while keeping the format

Copper Contributor

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

6 Replies

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


best response confirmed by DianaGrey (Copper Contributor)
Solution

@DianaGrey 

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 

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

 

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.
1 best response

Accepted Solutions
best response confirmed by DianaGrey (Copper Contributor)
Solution

@DianaGrey 

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

 

View solution in original post