Extract number only

[coolsac12]

Hi All,

I want to extract only number from a cell which have alphanumeric values. Please advise how can I do that.
OR
Please extract numbers in other cell and alphabets in another cell.

Thanks & regards,
Sachin Gupta

 

[Chihiro]

See attached.

 

[oldchippy]

If you also want the alphabets in another cell - assuming just one each end

=LEFT(D2,1)&RIGHT(D2,1)

 

[Asheesh]

Sachin,

Put the below in E2 and drag it down...

LOOKUP(9^9,--MID(D2,MIN(IFERROR(FIND(ROW($1:$10)-1,D2),"")),ROW(INDIRECT("1:"&LEN(D2)))))

To be acknowledged with CSE

 

[coolsac12]

Thank you so much all.. for amazing answers.

Regards,
Sachin Gupta

 

[Asheesh]

You welcome...

 

[mohadin]

if you please see the attached
tel me about the Idea?
thanks

 

[Asheesh]

What do you want to know...can you please explain

 

[Asheesh]

If you just want to extract the remaining text...use the below in K2 (as per your attached)

SUBSTITUTE(D2,H2,"")

 

[Khalid NGO]

Hi Sachin,
I have noticed you have just 3 type of text ["D","R","-"]
You can try the following also:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(D2),"D",""),"R",""),"-","")

If you have more strings, go with above solutions.

Regards,

 

[coolsac12]

Hi Sachin,
I have noticed you have just 3 type of text ["D","R","-"]
You can try the following also:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(D2),"D",""),"R",""),"-","")

If you have more strings, go with above solutions.

Regards,

Thanks @Khalid NGO ,

Its nice...