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

Sandler

Board Regular
Joined
Sep 15, 2011
Messages
165
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 () .
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
... 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?
 
Upvote 0
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.
 
Upvote 0
You can add the K in custom format not sure how to reduce by 100 and mean the same
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top