search macro/formula help

[refresh]

Hiya,

I'm trying to find some info from a stream of data. There is a lot of info
and it varies from one line to next so I can't target set start points in the
string by using MID.

Ideally, from the following I would be able to pick out and return 1234567.

ancekdgkhglsdkgldfgkjdflgdg7890gafgagdfg205056agadgafgafg1234567adfgdfg32adfgadfg54gaadfgadga329adfgafgag654123afga

The number is arbitrary as it would be a different and unknown number in
every case. The only constant is the length and format of the string I'm
looking for. I've got 30,000+ of these strings and I'd like to know if there
is a way to pick out the number in excel or if there is something else i
should look at that might help.

I'd appreciate any assistance you can offer.

Many thanks.




Is what I'm asking possible and are there any quick ways to run this?

[Carim]

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&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

HTH
Cheers
Carim

[Toppers]

Try:

A1=text string
ancekdgkhglsdkgldfgkjdflgdg7890gafgagdfg205056agadgafgafg1234567adfgdfg32adfg........
B1=Search string (1234567)

=IF(ISERROR(MID(A1,SEARCH(B1,A1),LEN(B1))),"",MID(A1,SEARCH(B1,A1),LEN(B1)))

or

=IF(ISERROR(MID(A1,SEARCH(B1,A1),LEN(B1))),"",B1)

HTH

"refresh" wrote:

> Hiya,
>
> I'm trying to find some info from a stream of data. There is a lot of info
> and it varies from one line to next so I can't target set start points in the
> string by using MID.
>
> Ideally, from the following I would be able to pick out and return 1234567.
>
> ancekdgkhglsdkgldfgkjdflgdg7890gafgagdfg205056agadgafgafg1234567adfgdfg32adfgadfg54gaadfgadga329adfgafgag654123afga
>
> The number is arbitrary as it would be a different and unknown number in
> every case. The only constant is the length and format of the string I'm
> looking for. I've got 30,000+ of these strings and I'd like to know if there
> is a way to pick out the number in excel or if there is something else i
> should look at that might help.
>
> I'd appreciate any assistance you can offer.
>
> Many thanks.
>
>
>
>
> Is what I'm asking possible and are there any quick ways to run this?

[Bill Foster]

From that example, how is it that you determined you wanted the 1234567 from
the data as opposed to the 205056 or any of the other number sequences in
that data?

Is the length of the number always 7 digits?
Is it always the 2nd seven digit number in the stream?

Bill

"refresh" wrote:

> Hiya,
>
> I'm trying to find some info from a stream of data. There is a lot of info
> and it varies from one line to next so I can't target set start points in the
> string by using MID.
>
> Ideally, from the following I would be able to pick out and return 1234567.
>
> ancekdgkhglsdkgldfgkjdflgdg7890gafgagdfg205056agadgafgafg1234567adfgdfg32adfgadfg54gaadfgadga329adfgafgag654123afga
>
> The number is arbitrary as it would be a different and unknown number in
> every case. The only constant is the length and format of the string I'm
> looking for. I've got 30,000+ of these strings and I'd like to know if there
> is a way to pick out the number in excel or if there is something else i
> should look at that might help.
>
> I'd appreciate any assistance you can offer.
>
> Many thanks.
>
>
>
>
> Is what I'm asking possible and are there any quick ways to run this?

[refresh]

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&"0123456789")),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
>
>

[refresh]

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&"0123456789")),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
>
>