Concatenate a letter into a number and preserve the number's format

[Sandler]

I would like to concatenate a letter at the end of a number and preserve the number's current format. The number is rounded into thousandths. So if i am talking about $(1,500), I would like to see $(1.5)K. Unfortunately, when the K is added it turns into -1.5K, removing both the $ and the () .

 

[Meggesto]

So based on your example above this is how I got there:

1. Starting with $(1,500.00) in Cell A1
2. Then B1 =ABS(A1/1000). This got me the 1.5 and dropped the negative that automatically formats because of the parenthesis.
3. Then C1 =TEXT(B1, "$*(###0.0)"). This got me the $(1.5)
4. Assuming D1 = K
5. Then E1 = C1&D1. This got me $(1.5)K

You'll have to insert a few columns to do this and drag your formulas down through the rows but once you've gotten everything formatted the way you like just Copy and Paste as Values over your final column to set the values. You can then delete all the additional rows you used to arrive at the final format.

 

[mole999]

is it always K?

 

[pgc01]

Unfortunately, when the K is added it turns into -1.5K, removing both the $ and the () .

Hi

How are you adding the K?

Can you post the number format you tried?

 

[pgc01]

... and also

- you want to enter -1500 in a cell and see it displayed as $(1.5)K just using the number format?
or
- you want to enter -1500 in a cell and then have $(1.5)K displayed in another cell using a formula?

 

[Sandler]

Thanks for all the responses.

The number can be positive or negative. It will always be K (showing amounts in thousandths).

I do eventually want to move the number to another part of the spreadsheet via reference.

I have the rounding part down. I just wanted to concatenate the K, and have excel still treat the cell as a number, so it won't change the format.

Currently I have the K in another column (in a small cell) which should be fine, if i can't resolve this.

 

[mole999]

You can add the K in custom format not sure how to reduce by 100 and mean the same

 

[Rick Rothstein]

Thanks for all the responses.

The number can be positive or negative. It will always be K (showing amounts in thousandths).

I do eventually want to move the number to another part of the spreadsheet via reference.

I have the rounding part down. I just wanted to concatenate the K, and have excel still treat the cell as a number, so it won't change the format.

Currently I have the K in another column (in a small cell) which should be fine, if i can't resolve this.

Assuming if you non-rounded number (either 1500 or -1500) is in cell A1, formatting the cell with this custom format will make the number display the way you want (then value in the cell will still be the original value)...

$0.0,K;$(0.0,)K

If you want the formatted number displayed in another cell (so that A1 shows 1500 but the formula cell shows $1.5K), then use this formula...

=TEXT(G1,"$0.0,K;$(0.0,)K")

 

[Sandler]

Thanks, Rick.

I like it. The format is perfect.

The only thing is, now, I can't select a few of those numbers and get an automatic sum.