Using OR with a Find

[Bruce Robeson]

MARSHALL H 7:30
NEVILLE LA A 7:00


In order to remove everthing after Marshall in the top row
and Neville LA in the 2nd row, I can enter the following
formula:

=LEFT(E3,FIND("H ",E3,1)-1) yields Marshall, but I have to
change the "H " part to "A " to work on the 2nd line to
get what I want. So, I tried an or combination as follows
but it does not work. Obviouslly I want it find whichever
one occurs, be it the A or the H.

=OR(LEFT(E3,FIND("H ",E3,1)-1),LEFT(E3,FIND("A ",E3,1)-1))
This just yields #Value! for its answer.

 

[Frank Kabel]

Hi
try
=IF(ISNUMBER(FIND("H",E3,1),LEFT(E3,FIND("H ",E3,1)-1),LEFT(E3,FIND("A
",E3,1)-1))

 

[Don Guillett]

hopefully you only have a & h
=LEFT(B2,IF(ISERR(FIND("A ",B2,1)),FIND("H ",B2,1),FIND("A ",B2,1))-2)

 

[hgrove]

Bruce Robeson wrote...

MARSHALL H 7:30
NEVILLE LA A 7:00

In order to remove everthing after Marshall in the top row and
Neville LA in the 2nd row, I can enter the following formula:

=LEFT(E3,FIND("H ",E3,1)-1) yields Marshall, but I have to
change the "H " part to "A " to work on the 2nd line to get what
I want. So, I tried an or combination as follows but it does not
work. Obviouslly I want it find whichever one occurs, be it the A
or the H.

Only H or A? If so,

=IF(OR(COUNTIF(A6,{"* H *","* A *"})),LEFT(A6,
MIN(SEARCH({" H "," A "},A6&{" H "," A "}))),"")

This is easily extended. If you also needed to look for X,

=IF(OR(COUNTIF(A6,{"* H *","* A *","* X *"})),LEFT(A6,
MIN(SEARCH({" H "," A "," X "},A6&{" H "," A "," X "}))),""

 

[Bruce Roberson]

Thanks too all three for your replys. As it turns out,
this reply from "hgrove" was the one that worked and the
others still gave #value!...


Not sure the name of the author, but thanks...


Bruce

 

[Don Guillett]

I agree that Harlan's was better if there wasn't an A or an H. However, this
WAS tested with the data given.
=LEFT(B2,IF(ISERR(FIND(" A ",B2,1)),FIND(" H ",B2,1),FIND(" A ",B2,1)))