How to extract numbers from text in excel

Question

I have following pattern of string in Excel

 Salary
 Rs 4.5 Lakhs
 Rs 8.2 Lakhs
 Rs 2.5 Lakhs
 Rs 3 Lakhs

I want to extract numbers from above string

 Salary          New Salary
 Rs 4.5 Lakhs    4.5
 Rs 8.2 Lakhs    8.2 
 Rs 2.5 Lakhs    2.5 
 Rs 3 Lakhs      3

What formula I can use to extract numbers from the string?

What have you tried? Did you also look in here for similar questions? Have you looked into `MID` & `SEARCH` or `FILTERXML` to split by delimiter — P.b, Feb 26 '22 at 15:35
I have tried `MID(K2,FIND(" ", K2)+1,256)` but this is extracting `4.5 Lakhs` not `4.5` — Neil, Feb 26 '22 at 15:42
@Neil please refer the answer i have posted, that should serve the purpose, however there other ways to solve it, — Mayukh Bhattacharya, Feb 26 '22 at 15:43
As mentioned by @P.b you can use `FILTERXML` in this way `=FILTERXML("~~"&SUBSTITUTE(A2," ","")&"~~","//s[.*0=0]")` — Mayukh Bhattacharya, Feb 26 '22 at 15:52
For more on `FILTERXML` you may refer the whole explanation by @JvDV Sir --> https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml — Mayukh Bhattacharya, Feb 26 '22 at 15:53

score 1 · Answer 1 · answered Feb 26 '22 at 15:41

1

You may try a formula like this using SUBSTITUTE & REPLACE function

Formula used in cell B2

=--REPLACE(SUBSTITUTE(A2," Lakhs",""),1,3,"")

And Fill Down!

answered Feb 26 '22 at 15:41

1 Answers1