finding char within a cell
 

I posted this a while ago and it never showed up. I apoligize if it
duplicates itself.

I have a list of account numbers in column A.

JOR123
1234
567US/789US
PB34-7890

I need the text or numbers before the - or the / I am using the find
function to get the poistion along with the right function to pull out the
info. The problem is I dont know how to look for the - or the /. I can only
do one or the other.

Thanks for your help

One way:

=LEFT(A1,FIND("/",SUBSTITUTE(A1,"-","/"))-1)

or if you need to take all of the number if there is no / or -:

=IF(ISERR(FIND("/",SUBSTITUTE(A8,"-","/"))),A8,
LEFT(A8,FIND("/",SUBSTITUTE(A8,"-","/"))-1))

In article ,
Jordan wrote:

I posted this a while ago and it never showed up. I apoligize if it
duplicates itself.

I have a list of account numbers in column A.

JOR123
1234
567US/789US
PB34-7890

I need the text or numbers before the - or the / I am using the find
function to get the poistion along with the right function to pull out the
info. The problem is I dont know how to look for the - or the /. I can only
do one or the other.

Another way...

=IF(OR(ISNUMBER(SEARCH({"/","-"},A1))),LEFT(A1,MIN(SEARCH({"/","-"},A1&"/
-"))-1),A1)

Hope this helps!

In article ,
Jordan wrote:

I posted this a while ago and it never showed up. I apoligize if it
duplicates itself.

I have a list of account numbers in column A.

JOR123
1234
567US/789US
PB34-7890

I need the text or numbers before the - or the / I am using the find
function to get the poistion along with the right function to pull out the
info. The problem is I dont know how to look for the - or the /. I can only
do one or the other.

Thanks for your help

Domenic wrote...
Another way...

=IF(OR(ISNUMBER(SEARCH({"/","-"},A1))),LEFT(A1,MIN(SEARCH({"/","-"},
A1&"/-"))-1),A1)
....

If you're going to append "/" and "-" to A1, there's no need for the
error trap.

=LEFT(A1,MIN(FIND({"-","/"},A1&{"-","/"}))-1)

Ahh yes... Thanks Harlan!

In article . com,
"Harlan Grove" wrote:

Domenic wrote...
Another way...

=IF(OR(ISNUMBER(SEARCH({"/","-"},A1))),LEFT(A1,MIN(SEARCH({"/","-"},
A1&"/-"))-1),A1)
...

If you're going to append "/" and "-" to A1, there's no need for the
error trap.

=LEFT(A1,MIN(FIND({"-","/"},A1&{"-","/"}))-1)