PDA

View Full Version : Solved: Conditional Concatenate?



djbleaks
09-22-2010, 07:58 AM
Hello all,

I have four columns with different attributes I am trying to concatenate into one column. The problem is, one of these columns has fields with an "&" symbol and proceeding name that I would not like to include in the final merged field. For example, there is:

Joe & Joan | B. | Smith | Jr.
Tim | C. | Tiny | III.

and I would like:

Joe B. Smith, Jr.
Tim C. Tiny, III.

So the question is, how can you remove "& Joan" (or & ANYCHAR)? If it can be done in a separate step (aside from final concatenate) that would work too. Any help or feedback is appreciated!

Thank you

Bob Phillips
09-22-2010, 08:11 AM
Try

=LEFT(A1,IF(ISNUMBER(FIND("&",A1)),FIND("&",A1)-2,LEN(A1)))&" "
&LEFT(B1,IF(ISNUMBER(FIND("&",B1)),FIND("&",B1)-2,LEN(B1)))&" "
&LEFT(C1,IF(ISNUMBER(FIND("&",C1)),FIND("&",C1)-2,LEN(C1)))&" "
&LEFT(D1,IF(ISNUMBER(FIND("&",D1)),FIND("&",D1)-2,LEN(D1)))

djbleaks
09-22-2010, 09:57 AM
It works! Thank you so much!

This is my new favorite forum. There's so much to learn! :thumb

Bob Phillips
09-22-2010, 03:23 PM
It works! Thank you so much!

This is my new favorite forum. There's so much to learn! :thumb

It's been my favourite forum for years :)