Convert text to number in Google Sheets & Excel

Question

How can i convert these strings to numbers in google sheets & excel, considering the columns is a mixture of billions and millions. $9B $100M $100M $90B

Are each number in a different cell? I mean, is the formula you need something to convert "$9B" to 9000000000, next cell "$100M" to 100000000 and so on? — Fernando Barbosa, May 01 '22 at 23:29
yes, i want to convert B's to 9 '0' & M to 6 '0'.The strings are in one column. — Anthony Ndungu, May 01 '22 at 23:42

score 1 · Accepted Answer · answered May 02 '22 at 01:12

1

try:

=INDEX(IF(REGEXMATCH(A1:A4&""; "M");
 REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000000; 
 IF(REGEXMATCH(A1:A4&""; "k");
 REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000;
 IF(REGEXMATCH(A1:A4&""; "B");
 REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000000000; A1:A4))))

answered May 02 '22 at 01:12

player0

124,011
12
67
124

@Mayukh Bhattacharya Should i use the 6 formulas or choose one randomly? – Anthony Ndungu May 02 '22 at 01:32
1

@AnthonyNdungu that is one single formula (just on six rows) – player0 May 02 '22 at 02:00
1

@AnthonyNdungu https://docs.google.com/spreadsheets/d/1DVd4WTnTcCZaqmRj20PDlqVISD1xYPDzZBzW8qFRDFc/edit#gid=0 – player0 May 02 '22 at 02:03

score 0 · Answer 2 · answered May 02 '22 at 00:11

0

For the columns whose values are consistence eg $100B, $10B, $4B...and so on this formula works =SPLIT(lower(A3),"qwertyuiopasdfghjklzxcvbnm`-=[];',./!@#$%^&*()") in google sheets.

answered May 02 '22 at 00:11

Anthony Ndungu

23
2

Convert text to number in Google Sheets & Excel

2 Answers2