Microsoft Excel - A1= "Highland 750 ml" & I want result in B2= 750 what is tha excel fomula to do this

Asked By udara udayanga on 27-Oct-12 04:10 AM

A1= Highland 750 ml

I want a result in B2= 750

it means iwant to seperate number from a alphanumeric sentence...

ex.. asdf llkk 2502033 kkj mmmn

i want to seperate 2502033 from above sentence...

what is the excel fomula to do this thing?

Harry Boughen replied to udara udayanga on 27-Oct-12 03:33 PM

Hello Udara

This will work with your headline example.

=LEFT(RIGHT(A1,FIND(" ",A1)-3),FIND(" ",RIGHT(A1,FIND(" ",A1)-3))-1)

If you want something more general it is a bit more complicated.

Regards

Harry

Harry Boughen replied to udara udayanga on 27-Oct-12 04:07 PM

Hello again Udara,

This is a more general formula.

=REPLACE(LEFT(A1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(A1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0

If your strings can be longer than 30 characters change the INDIRECT range to suit.

Regards

Harry