Nesting SUBSTITUE function with MULTIPLY parameter not working in Google Sheets

Question

I currently have a sheet with numbers displayed in the "K,M,B" format (e.g: 1.2K, 5M, 1.3B).

And I am currently trying to make a function that converts this to the numerical values on a separate sheet. For example, 1.2k would be displayed as 1200 and so on.

Currently I have: =SUBSTITUTE(Shorts!B2,"K","")*1000

However, I would also like this SUBSTITUTE functions to handle the case for M (million) and B (billion) so I can drag the cell down the column.

But when I add more multiplications to the nested functions

=SUBSTITUTE(SUBSTITUTE(Shorts!B2,"K","")*1000,("M","")*1000000

it doesn't seem to work and I get a formula parse error.

Any guidance would be much appreciated.

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