Hi!

BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05

Where does the account number end in the 3rd example?

If all strings were like the first 2 this would be relatively easy. If the
account number always ends before the 2nd space this should not be too
difficult. Find the 1st digit, find the 2nd space, return everything
between. But, if some stings might look like this:

ACCT 111213 1415 9/7/05 - 10/6/05
BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05
XX 111213 1415 04 9/7/05 - 10/6/05

There may be too many variables to consider.

Biff

"Rbp9ad" wrote in message
...
I need a formula that returns the account number and only the account
number from a text string that looks like this.
BAN 123456 10/1/05-10/31/05
or
ACCT 78-910 9/25/05 - 10/26/05
or
111213 1415 9/7/05 - 10/6/05
the formula that I have so far is this.
=TRIM(MID(TRIM(RRLookup(A9,11)),FIND("9",TRIM(RRLo okup(A9,11))),FIND("/",RRLookup(A9,11))-2))
RRLookup is a custom function that returns text strings like the examples.
This formula is for an account number that begins with 9.
I want it to be more general and just start at the first numeric
character. Is there a way to do this?