PDA

View Full Version : Extracting information after B/C



satyen
07-19-2008, 08:00 AM
Hello,

I was wondering if anyone can help. I am having trouble with a formula. I have most of it. But finding it difficult to combine with the rest of the formula.

This is what I am trying to do.

Take value from column A where present, otherwise take value from column B following SPACE after "B/C"(need to remove SPACES in between derived value)

=IF(A2<>"",IF(ISERROR(MID(A2,SEARCH("B/C",A2,1)+4,LEN(A2)))=TRUE,A2,SUBSTITUTE(MID(A2,SEARCH("B/C",A2,1)+4,LEN(A2))," ","")))

Need to add some more to incorporate where there is no value in A so it takes from B. Again value is taken following SPACE after "B/C"(need to remove SPACES in between derived value)

This is what A or B could contain
Company B/C H O ME

Result should be HOME

Many Thanks in advance

Bob Phillips
07-19-2008, 08:35 AM
=IF(A2<>"",IF(ISNUMBER(SEARCH("B/C",A2)),SUBSTITUTE(MID(A2,SEARCH("B/C",A2,1)+4,LEN(A2))," ",""),A2),
IF(B2<>"",IF(ISNUMBER(SEARCH("B/C",B2)),SUBSTITUTE(MID(B2,SEARCH("B/C",B2,1)+4,LEN(B2))," ",""),B2),""))