Use FIND for / or \ or -
 

appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of a
marker which could be / or \ or -
I can only see how to use FIND for one character how to use for multiple
possibilites? (so when FIND reaches / or \ or - it returns the position)
Thanks for any suggestions
Bert

Use FIND for / or \ or -
 

Try this:

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

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Bert" wrote in message
...
appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of a
marker which could be / or \ or -
I can only see how to use FIND for one character how to use for multiple
possibilites? (so when FIND reaches / or \ or - it returns the position)
Thanks for any suggestions
Bert

Use FIND for / or \ or -
 

Instead of using:
=FIND()
use:
=IF(ISERROR(FIND("-",A1,1)),0,FIND("-",A1,1))+IF(ISERROR(FIND("\",A1,1)),0,FIND("\",A1, 1))+IF(ISERROR(FIND("/",A1,1)),0,FIND("/",A1,1))

--
Gary''s Student - gsnu200762


"Bert" wrote:

appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of a
marker which could be / or \ or -
I can only see how to use FIND for one character how to use for multiple
possibilites? (so when FIND reaches / or \ or - it returns the position)
Thanks for any suggestions
Bert

Use FIND for / or \ or -
 

Playing off of RD's really nifty solution, this one will return a blank if
there is no occurance of \, /, or- in the cells

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

Vaya con Dios,
Chuck, CABGx3



"RagDyeR" wrote:

Try this:

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

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Bert" wrote in message
...
appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of a
marker which could be / or \ or -
I can only see how to use FIND for one character how to use for multiple
possibilites? (so when FIND reaches / or \ or - it returns the position)
Thanks for any suggestions
Bert

Use FIND for / or \ or -
 

Another way...

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


"Bert" wrote:

appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of a
marker which could be / or \ or -
I can only see how to use FIND for one character how to use for multiple
possibilites? (so when FIND reaches / or \ or - it returns the position)
Thanks for any suggestions
Bert

Use FIND for / or \ or -
 

Thanks for all your solutions as I see a use for them in other work

Bert

"Teethless mama" wrote:

Another way...

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


"Bert" wrote:

appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of a
marker which could be / or \ or -
I can only see how to use FIND for one character how to use for multiple
possibilites? (so when FIND reaches / or \ or - it returns the position)
Thanks for any suggestions
Bert

Use FIND for / or \ or -
 

Why not just pick one of the search characters and then do only two
substitutions (one for each of the other characters)...

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

Rick


"Teethless mama" wrote in message
...
Another way...

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


"Bert" wrote:

appy New Year all,
I want to use the FIND function with LEFT to extract the numbers left of
a
marker which could be / or \ or -
I can only see how to use FIND for one character how to use for multiple
possibilites? (so when FIND reaches / or \ or - it returns the position)
Thanks for any suggestions
Bert

Use FIND for / or \ or -
 

CLR wrote...
Playing off of RD's really nifty solution, this one will return a
blank if there is no occurance of \, /, or- in the cells

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

No need for multiple FIND calls.

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