Search code examples
excelexcel-formulaformattingformatdataset

How to convert 1 Cr to 10000000 in excel

I have a dataset column with following value
1.01 Cr
20.5 L
2.51 Cr
3.16 Cr
1.68 L
.
.
and so on

(1 Cr = 10000000, 1 L = 100000)

how do I unformat this number for example, 1.01 Cr to 10100000 and 20.5 L to 2050000
I refered this youtube video https://www.youtube.com/watch?v=DuqBhwzWqBU&ab_channel=JigneshGupta
and tried =IFNA(LEFT(C2,LEN(C2)-1)CHOOSE(MATCH(RIGHT(C2,1),{"L","Cr"},0),100000,10000000),C2) but its not working.
I know nothing about excel let me know that formula should I use?

Dataset sample Image refer this img for sample view

Solution

  • The formula you are using looks to the last character in the string. CR is two characters and as such you will never match. Instead, split on the space:

    =LEFT(A1,FIND(" ",A1)-1)*CHOOSE(MATCH(MID(A1,FIND(" ",A1)+1,LEN(A1)),{"L","Cr"},0),100000,10000000)

    enter image description here

    With the newer functions currently being released we can use TEXTBEFORE and TEXTAFTER instead:

    =TEXTBEFORE(A1," ")*CHOOSE(MATCH(TEXTAFTER(A1," "),{"L","Cr"},0),100000,10000000)