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

Sandler

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

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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,750
... 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
162
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
9,899
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
35,504
Office Version
2010
Platform
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
162
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,082,115
Messages
5,363,247
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top