Help with formula

[blackie42]

Hi

Trying to figure out how I get the following result (need formulae in columns B & C) from input in to Column A



ColA
ColB
ColC


A
A



B

B


A_B
A
B



Any help much appreciated

thanks
Jon

[mancubus]

assuming Row 1 contains headers and actual values in Colum A are "A" or "B" or "A_B":

Formula in A2:

=IF(OR(A2="A",A2="A_B"),"A","")

Formula in B2:

=IF(OR(A2="B",A2="A_B"),"B","")

copied down.

[mancubus]

OR

Formula in A2:

=IF(ISNUMBER(SEARCH("A",A2)),"A","")

Formula in B2:

=IF(ISNUMBER(SEARCH("B",A2)),"B","")

copied down.



or other methods...

[mancubus]

for case-sensitive lookup, use FIND function rather than SEARCH function.

[petroj02]

for cell B1


=if(or(A1="A";A1="A_B");"A";"")

for C1


=if(or(A1="B";A1="A_B");"B";"")

same for next rows but swap A1 to A2 or A3

[blackie42]

Thanks guys,

The IF IS NUMBER formula seems to work best

regards
Jon