Ron i am interested in identifying strings for initial fixes but your link is
broken.

"Ron Rosenfeld" wrote:

On Thu, 30 Oct 2008 06:21:00 -0700, mebsmith
wrote:

I have data in the following string format

BIGGY SBJ LANNA TROXL ETX FLOAT BOYER LRP GEERI KITHE BELAY TRISH CLIPR
ENSUE BAL
or
BIGGY|SBJ|LANNA etc
and

BWZ ELIOT ETX DIMMO DRAPE FORTT DOOTH PSB YNG CXR LEBRN
or
BWZ|ELIOT|ETX... etc..

I am trying to capture the first 5 letter "fix" in the string which is fine
if the first one is the 5 letter "fix". For that i have been using
Left(....,5) obviously.

But i want to be able to say that if the length before the first delimiter
is =3 then check after delimiter to get the first 5 letter "fix".

Unfortunately i cant for the life of me think how to accomplish this.

Any thoughts?

Thanks

Marcus



but also with

It appears as if you will be parsing out flight plans, which may lead you into
a requirement for more than a "one-off" solution.

So long as the strings will be less than 256 characters, one simple way would
be to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

and then use this formula which will return the first 5 letter string
consisting of all capital letters.

=REGEX.MID(A1,"[A-Z]{5}")

=REGEX.MID(A1,"[A-Z]{5}",2) would return the second 5 letter string, etc.

You can also easily modify the pattern to detect Victor airways, Jet routes, Q
routes, etc, if that is something you will be getting into.

If the strings might be longer than 255 characters, this function can be
written as a UDF.
--ron