How Can I Reverse Abbreviated Numbers?

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
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
:cool:
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
How about
=IF(ISNUMBER(FIND("M",A1)),SUBSTITUTE(A1,"M","")*10^6,IF(ISNUMBER(FIND("K",A1)),SUBSTITUTE(A1,"K","")*1000,A1))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,568
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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! (y)

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
Joined
Nov 27, 2012
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,774
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

How about just find and replace?

Replace K with E3 and M with E6.
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,774
Office Version
  1. 2010
Platform
  1. Windows
Yes, it changes the number from text to scientific format.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,273
Messages
5,571,253
Members
412,374
Latest member
Nagelgal
Top