'Convert text to number in Google Sheets & Excel [duplicate]

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

arraysgoogle-sheets-formulastring-formattingnumber-formattingflatten


Solution 1:[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))))

enter image description here

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0
Solution 2 Anthony Ndungu

Related Questions