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
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