0

I'm having a bit of an issue analyzing data in Google sheets. One of the columns has abbreviated numbers with K (thousand), and M (million). The abbreviation also doesn't seem to be consistent. Here's an example of a few rows:

185K 
3.06K 
4.19K 
59.2K 
1.38M 
64K 
2.78K 
6.21K 
4.5K
41.7K 
516K 
85.1K 
21.8K 
516K 
235K

Is there an easy way for me to convert these into plain numbers, for example, 41.7K -> 41700 ?

Appreciate the help! Cheers

ross
  • 2,684
  • 2
  • 13
  • 22
Amir Shahzeidi
  • 13
  • 5
  • How is it "not consistent", I see no inconsistency in your sample. And are you sure the values are actually stored that way and not just displayed as such (i.e. what happens when you edit a field)? – Joachim Sauer Nov 08 '19 at 10:44
  • @JoachimSauer I can't say with certainty that they're inconsistent. The examples that I added here do seem consistent. So the data is stored and displayed as you see above. That's because these are the numbers that were scraped using ScreamingFrog. – Amir Shahzeidi Nov 08 '19 at 10:54
  • If there aren't too many of these numbers and you don't need to do this very often, it'd probably be easiest to copy the column into a text file and write an offline script to convert them, then paste them back. – AKX Nov 08 '19 at 10:58

1 Answers1

3

What better place to write a script to convert these strings to values than a spreadsheet.

Try

=if((right(A1,1))="K",(value(left(A1,(len(A1))-1)))*1000,if((right(A1,1))="M",(value(left(A1,(len(A1))-1)))*1000000,""))

Tedinoz
  • 5,911
  • 3
  • 25
  • 35