Search with or

[MRozell]

Using the following formula in conditional formatting.
=IFERROR(MOD(VALUE(LEFT(A1, SEARCH("/",A1)-1))-1,9)+1,MOD(A1-1,9)+1)=2
Does what I want.
Want to expand the search to look for "/" or " "
I tried adding in OR but it not working.

[hrlngrv]

Your formula is effectively =IFERROR(a+1,b+1)=2. Any reason it couldn't be =IFERROR(a,b)=1 ? Other simplications are possible.

As to the main issue, use an array to search for both, then use the minimum of the positions found. With some trickery, you don't need IFERROR.

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

Note that MOD automatically converts its arguments to numeric values, so VALUE isn't needed (unless you're using Transition Formula Evaluation, which you shouldn't be a decade and a half after IBM pulled the plug on Lotus 1-2-3).

[MRozell]

When I enter in the formula I get "You may not use unions, intersections, or array constants for conditional formatting criteria."

[bebo021999]

Maybe try:

Please Login or Register  to view this content.

[hrlngrv]

Sorry. I didn't pay enough attention to the conditional formatting part. One way would be to defined the names chars referring to =" /" and chara referring to ={" ";"/"}, then use the formula =MOD(LEFT(A1,MIN(FIND(chara,A1&chars))-1),9)=1 .

[Fluff13]

Another option
=MOD(LEFT(A2,MIN(SEARCH(" ",A2&" "),SEARCH("/",A2&"/"))-1)-1,9)

[MRozell]

Thanks bebo021999 the =MOD(LEFT(A1,IFERROR(IFERROR(SEARCH("/",A1),SEARCH(" ",A1)),LEN(A1)+1)-1)-1,9)+1 works perfectly.