PDA

View Full Version : [SOLVED] Concatenate a formatted number



paulked
02-04-2015, 12:27 PM
Hi there.

I have a number in cell A1 ranging from 0 to 100,000,000 and have formatted the cell to give me a shortened version of this number (eg 1,234 displays 1.2k and 1,234,567 displays 1.2M).

However, when I try to concatenate this with text from other cells it gives me the full display of the number (eg "1,234,567 items selected" instead of "1.2M items selected").

Any ideas?

Thanks

xld
02-04-2015, 12:35 PM
Use

=B1&TEXT(A1,"[>=1000000]#,##0.0,,\M;[>=1000]#,##0.0,K;##0")

paulked
02-04-2015, 12:56 PM
Many thanks, perfect