How To Make CONCATENATE Generate A Text String Using The Custom Formatted Value Of A Cell?

[tackyjan]

Hello,

I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).

number_forma.png

I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.

In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search):

[>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";

custom_format.png

As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.

Here is a screenshot of what the original and custom formatted cells look like:

custom_formatted_cells.png

As you can see the custom formatting works and does exactly what I wan

The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).

To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:

CONCATENATE("SOME STRING ", B1)

Where B1 is the custom formatted cell.

The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:

concatenate_formula.png

As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".

I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)

My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?

Thank you,

Jan

*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.