search macro/formula help
 

Hi,

Give it a try ...
formula is :
=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},D16&"01234 56789")),ROW(INDIRECT("1:"&LEN(A1)))))

HTH
Cheers
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=536930

search macro/formula help
 

Thanks all,

I'll try the formulas tomorrow and let you know how they go.

Bill, the only constant is that the sequence is 7 characters long and all
the characters are digits. I have nowt else to go on. The problem has come
up because I'm trying to extract data from ancient files and the definition
of the files has changed 50 or so times. To add to that I don't have
documentation for the changes so have nothing else to go on. I know that the
values are within each record/string, I just can't pinpoint where.

Cheers.

"Carim" wrote:


Hi,

Give it a try ...
formula is :
=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},D16&"01234 56789")),ROW(INDIRECT("1:"&LEN(A1)))))

HTH
Cheers
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=536930

search macro/formula help
 

Hi Carim, the formula returns #N/A. I have put the text string in A1 and
the formula in A2. Reading through the formula there is a ref to cell D16,
should there be a value in there?

Toppers, I think your formula depends on me knowing the actual values I'm
looking for and unfortunately I don't!

Thanks.

"Carim" wrote:


Hi,

Give it a try ...
formula is :
=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},D16&"01234 56789")),ROW(INDIRECT("1:"&LEN(A1)))))

HTH
Cheers
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=536930