I have a column of account numbers listed as follows:

US1234
6789
1234-12345
ABC/ABC62

What I am trying to do is to take the text and/or numbers before the "-" or
the "/".
I have tried using the find function which will give me the location and
then I can subtract that from the length and use the right fuction.

The problem I'm having is I cant figure out how to have the find function
return the location whether or not it's the - or the /.

Any help will be greatly appreciated.

Jordan

On Thu, 16 Jun 2005 15:20:03 -0700, Jordan
wrote:

I have a column of account numbers listed as follows:

US1234
6789
1234-12345
ABC/ABC62

What I am trying to do is to take the text and/or numbers before the "-" or
the "/".
I have tried using the find function which will give me the location and
then I can subtract that from the length and use the right fuction.

The problem I'm having is I cant figure out how to have the find function
return the location whether or not it's the - or the /.

Any help will be greatly appreciated.

Jordan

Try this:


=IF((COUNTIF(A1,"*-*")+COUNTIF(A1,"*/*"))=0,A1,
MID(A1,FIND(CHAR(1),SUBSTITUTE(
SUBSTITUTE(A1,"-",CHAR(1)),"/",CHAR(1)))+1,255))


--ron

How about using find/replace to change all the "/" into "-". Then use Data
Text to columns to separate the text into two pieces?
--
Gary's Student


"Jordan" wrote:

I have a column of account numbers listed as follows:

US1234
6789
1234-12345
ABC/ABC62

What I am trying to do is to take the text and/or numbers before the "-" or
the "/".
I have tried using the find function which will give me the location and
then I can subtract that from the length and use the right fuction.

The problem I'm having is I cant figure out how to have the find function
return the location whether or not it's the - or the /.

Any help will be greatly appreciated.

Jordan