Finding char within a cell

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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