How Can I Reverse Abbreviated Numbers?

[John Caines]

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)

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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

 

[Fluff]

How about
=IF(ISNUMBER(FIND("M",A1)),SUBSTITUTE(A1,"M","")*10^6,IF(ISNUMBER(FIND("K",A1)),SUBSTITUTE(A1,"K","")*1000,A1))

 

[Rick Rothstein]

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

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B
1
2	2M	2000000
3	3.8K	3800
4	27.4K	27400
5	254K	254000
6	14.4K	14400
7	n/a	n/a
8	953K	953000
9	429K	429000
10	115K	115000
11	17.9K	17900
12	35.5K	35500
13	2.4M	2400000
Sheet8

Cell Formulas
Range	Formula
B2:B13	B2	=IFERROR(LEFT(A2,LEN(A2)-1)*LOOKUP(RIGHT(A2),{"K","M"},{1000,1000000}),A2)

 

[John Caines]

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]

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:

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:

Sub LoopIt()

Dim rngCell As Range

For Each rngCell In Selection

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

End Sub

 

[shg]

How about just find and replace?

Replace K with E3 and M with E6.

 

[John Caines]

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]

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