2

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.

enter image description here

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.

2 Answers 2

1

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

enter image description here


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

enter image description here

1
  • 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.
    – Adam
    Nov 23, 2022 at 13:48
1

You could also try:

=ArrayFormula(--SUBSTITUTE(SUBSTITUTE(SPLIT(A2,"-"),"K","E3"),"M","E6"))

representing K and M in exponential notation.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.