• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract only numeric value.

R

Ramesh Deo

Member
Please extract only numerical value.
Ajit Sehrawat10001aaa
Annapurna Deori10002bbb
Deepak Kumar10003dddddd
Divya Dogra10004qq
Tarun Verma10005lll
Neelam Chauhan10006mmmmmm
Parijat Singal10007n
Shikha Maheshwari10008ss
 
Hi Ramesh,

Try below array formula:

=MID(B14,MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},B14),9.999E+307)),MAX(ISNUMBER(--MID(B14,ROW(INDIRECT("1:"&LEN(B14))),1))*ROW(INDIRECT("1:"&LEN(B14))))-MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},B14),9.999E+307))+1)

Considering your data in B14

Regards,
 
Hi Ramesh ,

In case your data is always going to have the number starting with the digit 1 , and of length 5 digits , you can consider a much simpler formula.

Narayan
 
Hi Sm & NK Sir,

Thanks a lot , its working nice.
Please explain in brief for above formula. I didn't get using 9.999E+307 in formula. so please explain all about.
 
@Ramesh Deo

This part of the formula is used to get the starting point from the string, from where we need to extract number.

MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},B14),9.999E+307))

Now this is simply checking any number from 0-9 in the text and if found, it will return it's position, if not found it will return an error. Since we want the first position of these numbers MIN function is used, but the whole function will give error if one error came as a result of SEARCH function, so to suppress the error IFERROR is used which will give the no. 9.999E+307 (biggest number Excel can understand) to it, so MIN function will ignore these numbers and will return the MIN or the first position where there is a number.

Regards,
 
You must log in or register to reply here.
Back
Top