function help please

  • Thread starter Thread starter KRK
  • Start date Start date
K

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
 
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},""))))
 
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)
 
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
Click to expand...
 
Back
Top