search macro/formula help

R

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?
 
T

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
 
C

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
 
B

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
 
R

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.
 
R

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.
 
Top