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

#### Sandler

##### Board Regular
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
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.

is it always K?

#### pgc01

##### MrExcel MVP
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

##### MrExcel MVP

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

ADVERTISEMENT

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

#### Rick Rothstein

##### MrExcel MVP
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
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.

Replies
19
Views
583
Replies
8
Views
125
Replies
0
Views
70
Replies
18
Views
376
Replies
1
Views
258

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

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