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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Meggesto

Board Regular
Joined
Mar 8, 2016
Messages
216
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
is it always K?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

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

Forum statistics

Threads
1,136,434
Messages
5,675,843
Members
419,586
Latest member
RoteichA

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
Top