0

I did some web-scraping and got data from a website on my Google Spreadsheet. The Data looks like this: 1.2M, 3.5M, 324k, 989k, 1.01M

I want to format them into ONLY digits. (M = Million / k = thousand) -> The finished table should look like this: 12000000 35000000 324000 ...

Is there a way to tell Google Spreadsheet that "if last character ="M/k" ... do this ... I can't come up with a good solution and I cant change the scraping results I get. I only recieve these numbers hardcoded as shown. (1.2M ... 342k...)

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Tim Bordasch
  • 29
  • 7

1 Answers1

0

First, separate the number from the letter in an adjacent column:

=LEFT(A1,LEN(A1)-1)

Then use IF to check which letter and multiple the adjacent column by 1,000,000 or 1,000:

=IF(Right(A1)="M",B1*1000000,B1*1000)

A       B       C
1.2M    1.2     1200000
3.5M    3.5     3500000
324K    324     324000
989K    989     989000
1.01M   1.01    1010000
Elliot11
  • 41
  • 3