# How can I turn £240K - £2.4M to 240000 2400000 on Google Sheets

dreadful title, sorry. I have a problem that I cannot solve despite trying for a couple of hours now.

I have a google sheet with a field that has values, like "£240K - £2.4M" - I've found a way to split these over two columns using:

``````=if(REGEXMATCH(L3,"-"),SPLIT(L3,CONCATENATE(SPLIT(L3,".0123456789"))),L3)
``````

So this will split them and remove the "£", "K", and "M". However, I also need to be able to convert £240k into 240000 rather than 240. I've found a way to do this using:

``````=REGEXEXTRACT(L3,"[\d\.]+")*10^IFERROR(VLOOKUP(REGEXEXTRACT(UPPER(L3),"K|M|B"),{"K",3;"M",6;"B",9},2,FALSE),1)
``````

What I want to be able to do is combine the two. So that I extract the value before and after the "-" but also convert this to be the full value.

Anyone able to offer some assistance with this?

Just to note, the values could be "£240K - £1.4M", "£240K - £400K", or £25M - £75M" so it's not always thousands and millions, it could be any combination. I just need to split them and format them for maths.

UPDATE: This formula would take both values, calculate and split them in two:

``````={ifna(regexextract(A1,"([0-9\.]+)K"=*1000),ifna(regexextract(A1,"([0-9\.]+)M")*1000000)}
``````

Try with this formula:

``````=ifna(regexextract(A1,"(\d+)K")*1000)+ifna(regexextract(A1,"(\d+)M")*1000000)
``````

It will take the numbers before K and multiply by 1000 and before M 1000000

• Hello, thank you for your reply. I've updated the original question with an image. I wanted the two values split over two cells, so: £240k - £2.4m in A1 becomes: 240000 in B1 and 2400000 in C1.
``````=ArrayFormula(--SUBSTITUTE(SUBSTITUTE(SPLIT(A2,"-"),"K","E3"),"M","E6"))