Convert text to number in Google Sheets & Excel

user_19011079 0
Viewed 116

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

google-sheets-formula flatten number-formatting string-formatting arrays
2 Answers

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

enter image description here

user_56326290

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

user_190110790
Related