function help please

[KRK]

Hello,

Can someone help me with a function to do this please ?

I have a cell with a text string in it, the cell is a mixture of numbers &
characters, eg 12ABC, or D45678. The numbers and characters are never mixed
up together (ie I never have AB12CD34).

I want to pick out the number part so I can do some calculations with it,

Can someone help me with a function to do it for me please?

Thanks

K

 

[Domenic]

Assuming that A2 contains the text string, try...

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUM(LEN(A2)-LEN(
SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

 

[Ron Coderre]

Since you want to subsequently perform a calculation,
I'm guessing the number to extract will be within Excel's
15 significant digit limitations...

If that's true, this formula returns the numeric
part of a string that either begins or ends with numbers:
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW($1:$15))))

Examples:
ABC0123....formula returns: 123
0123abc....formula returns: 123
ABC9876....formula returns: 9876

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[domenic22]

Need to add +0 at the end of the formula...

Assuming that A2 contains the text string, try...

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUM(LEN(A2)-LEN(
SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))







--Domenichttp://www.xl-central.com

 

[KRK]

As always thanks for the help

KK