Here's a shorter formula for extracting consecutive numbers from anywhere in
a string:
A1: (string containing consecutive numbers, eg abc123xyz)

B1:=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01 23456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1) ),1))))

It replaces my previously posted:
LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"012 3456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))


***********
Regards,
Ron

XL2002, WinXP-Pro


"Monk" wrote:

Thanks to all who took time to reply..
Ron, your formual works brill.. Thanks again ... Monk *





"Ron Coderre" wrote:

OK...see if this works:
A1: (some string containing consequtive numbers)
B1:=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Monk" wrote:


Hello,
I attempted to edit the first post, but it has posted a new thread..

To clear up the situation.. It appears the only characters mixed within the
numbers is the €˜character which sometimes appears at the beginning and end of
the set of numbers (but not consistent). So just need to remove the €˜
characters.. ?

I have tried your method Ron (with thanks), but as you mentioned it only
works if there is no €˜ characters to the right of the numbers, which
unfortunately there is on some of the entries.

Monk..!