# How Can I Reverse Abbreviated Numbers?

#### John Caines

##### Well-known Member
Hello All,
I have a simple row of numbers (421 rows).
A text file of the list of numbers is here in my dropbox (it's just a simple text file)
As an example; here is a slection;
--------------
2M
3.8K
27.4K
254K
14.4K
n/a
953K
429K
115K
17.9K
35.5K
2.4M
---------------
So there is the word N/A in the list.
And thousands are formatted with a K
Millions with a M

My question is;
How can I convert this list into reall numbers?
So it would actually show the thousands and millions?

So it should look like this
-------------------
2,000,000
3,800
27,400
254,000
14.400
n/a
953,000
429,000
115,000
17.900
35.500
2.400,000
------------------
I did try Googling, but I kept getting answewrs to do the opposite, IE change 1,000,000 to 1M

Hope someone can help me out here,.
I'm really stumped!
Can't find a solution to this anywhere I've looked.
Hope someone can help me out here.

Many thanks all.
Best regards
John C

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Fluff

##### MrExcel MVP, Moderator
=IF(ISNUMBER(FIND("M",A1)),SUBSTITUTE(A1,"M","")*10^6,IF(ISNUMBER(FIND("K",A1)),SUBSTITUTE(A1,"K","")*1000,A1))

#### Rick Rothstein

##### MrExcel MVP
It is not clear if you want a VBA solution to convert them directly within their cells (disadvantage is you lose the ability to check if the conversion worked) or a formula solution (which you could copy/paste over the original data once satisfied). Here is a formula solution that will work...

=IF(A1="n/a","n/a",IF(RIGHT(A1)="M",SUBSTITUTE(A1,"M","")*1000000,IF(RIGHT(A1)="K",SUBSTITUTE(A1,"K","")*1000,A1)))

#### John Caines

##### Well-known Member

Hello All !!!!
OMG!
Quick replies from everyone here,,
Really grateful for this,,, brilliant stuff!!

I've just tried all 3,, and all 3 work fine!!!
No my problem is in choosing which 1 formula to use!

Again, really grateful to you all, Fluff / Rick / Eric, many thanks for your help on this.

I just couldn't find in on Google or youtube or here!
Maybe I was using the wrong search terms.

At least I can now sort the column correctly now.

Have a great day all.

A very grateful
John C

#### Wookiee

##### Active Member
It looks like by the time I wrote and tested my solution, I was beat to the punch not once but thrice. But just in case you'd like a VBA method, I created this function which you might be able to employ:

VBA Code:
``````Function f_Numerize(strValue As String) As Long
'Created 17 Aug 2020 by Wookiee at MrExcel.com

'Declare Variables
Dim dblNumber As Double
Dim dblNA As Double
Dim dblK As Double
Dim dblM As Double
Dim dblB As Double
Dim strEvaluation As String

'Search For Text Values To Convert
dblNA = VBA.InStr(strValue, "N/A")
dblK = VBA.InStr(strValue, "K")
dblM = VBA.InStr(strValue, "M")
dblB = VBA.InStr(strValue, "B")

strEvaluation = VBA.UCase(strValue)

If dblNA > 0 Then

f_Numerize = 0
Exit Function

ElseIf dblK > 0 Then

dblNumber = VBA.CDbl(Replace(strValue, "K", ""))
f_Numerize = dblNumber * 1000
Exit Function

ElseIf dblM > 0 Then

dblNumber = VBA.CDbl(Replace(strValue, "M", ""))
f_Numerize = dblNumber * 1000000
Exit Function

ElseIf dblB > 0 Then

dblNumber = VBA.CDbl(Replace(strValue, "B", ""))
f_Numerize = dblNumber * 1000000000
Exit Function

End If

End Function``````

To test, I employed that function in a loop like this:

VBA Code:
``````Sub LoopIt()

Dim rngCell As Range

For Each rngCell In Selection

rngCell.Offset(, 1) = f_Numerize(rngCell.Value)

Next rngCell

End Sub``````

#### shg

##### MrExcel MVP

How about just find and replace?

Replace K with E3 and M with E6.

#### John Caines

##### Well-known Member
Hi there Wookiee & SHG!
(Long time no speak shg! Hope you are well!

Many thanks for both your replies.

Thank's for the VBA solution Wokkiee.
I've just tried it and it works fine,, so many thanks for coming up with this.
Very much appreciated.

==========================
And SHG, I never knew of this!
That's a really a handy excel trick to know.
Can I ask, what do you call this?

I mean if I want to find out more about this 'E3 and 'E6' idea, what would I type into Google to get some information on what you have suggested?
Is it scientific formatting?
If it is,all I'm finding is people converting scientific numbers to ordinary numbers and not how you have suggested.
I just wanted to see if anyone online had examples of what you suggested SHG, as it's a really cool trick to know, but I don't see it on Google.

Many thanks again for everyone's help here.
Page will be bookmarked!!

Have a great day all.
Stay Safe...
A very grateful
John C

#### shg

##### MrExcel MVP
Yes, it changes the number from text to scientific format.