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&"0123456 789")),SUM(LEN(A2)-LEN(
SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))


In article ,
"KRK" wrote:

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
http://www.xl-central.com

KRK;297472 Wrote:
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

Hi,
try the following

=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT( "1:100")),1)),0),
100-SUM(1*ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))) )*1

enter the formula as an arry formula with Ctrl+Shift+Enter


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=83142

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)

"KRK" wrote in message
...
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

As always thanks for the help

KK


"KRK" wrote in message
...
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