Concatenate

[arupghoshal]

Hello Saviors!

I am working on creating a Master Contact List collating data from various places. This is an exercise to create an invitation list and hence I keep coming back to take your help. My sincere gratitude to all of you.

To come to my problem, I am trying to use concatenate the following way to create a Address Label Head:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR="class: grid"]
[TD]Ms.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 221"]
<tbody>[TR="class: grid"]
[TD]Aloma[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR="class: grid"]
[TD]Ms. Aloma & [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Dr.[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 120"]
<tbody>[TR="class: grid"]
[TD]Ansari[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR="class: grid"]
[TD]Dr. & Ansari[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Mrs. & Mr.[/TD]
[TD][TABLE="width: 221"]
<tbody>[TR="class: grid"]
[TD]Amit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 143"]
<tbody>[TR="class: grid"]
[TD]Pooja[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 120"]
<tbody>[TR="class: grid"]
[TD]Advani[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR="class: grid"]
[TD]Mrs. & Mr. Amit & Pooja Advani[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 221"]
<tbody>[TR="class: grid"]
[TD]Aryaman & Amaya[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 120"]
<tbody>[TR="class: grid"]
[TD]Aggarwal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR="class: grid"]
[TD] Aryaman & Amaya & Aggarwal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, I have given above 4 different examples. The formula I am using in the "Label Heading" Column is - =CONCATENATE(B2," ",C2," & ",D2," ",E2).

Here, since I am using (space) and (&) texts, the Labels headings are populated the same way.

What I would ideally like is that the above column is populated as:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]Label Heading[/TD]
[/TR]
[TR]
[TD]Ms. Aloma[/TD]
[/TR]
[TR]
[TD]Dr. Ansari[/TD]
[/TR]
[TR]
[TD]Mrs. & Mr. Amit & Pooja Advani[/TD]
[/TR]
[TR]
[TD]Aryaman & Amaya Agarwal[/TD]
[/TR]
</tbody>[/TABLE]

Is the above result possible? If so, would appreciate your guidance.

Thanks & Regards,
Arup Ghoshal

 

[James006]

Hello,

You could test =Trim(CONCATENATE(B2," ",C2," & ",D2," ",E2))

Hope this helps

 

[arupghoshal]

Thanks pal, but no, this does not take care of the extra text "&". It should not take this text if the adjacent cell is blank.

 

[Rick Rothstein]

Thanks pal, but no, this does not take care of the extra text "&". It should not take this text if the adjacent cell is blank.

Give this formula a try...

=TRIM(B2&" "&C2&IF(LEN(D2)," & "&D2,"")&" "&E2)

 

[majid_mx4]

Hi,
You can use this formula

=TRIM(IF(AND(D2<>"",C2<>""),CONCATENATE(A2," ",B2,"  &  ",C2,"  ",D2),CONCATENATE(A2," ",B2,"  ",C2," ",D2)))

Thx Mir

 

[Michael M]

"pal" really isn't an appropriate term to use for a fellow user.....but try

=TRIM(IF(OR(D2="",E2=""),B2&" "&C2&" "&D2&" "&E2,B2&" "&C2&" &"&" "&D2&" "&E2))

 

[arupghoshal]

"pal" really isn't an appropriate term to use for a fellow user.....but try

=TRIM(IF(OR(D2="",E2=""),B2&" "&C2&" "&D2&" "&E2,B2&" "&C2&" &"&" "&D2&" "&E2))

My apologies if I hurt someone by using an inappropriate word. I meant "pal" as a friend. (https://dictionary.cambridge.org/dictionary/english/pal)

Thanks everyone, will try the above formula and hope it works. Will let you all know how it went.

Warmest Regards,
Arup

 

[Rick Rothstein]

Thanks everyone, will try the above formula and hope it works. Will let you all know how it went.

What about the formulas in Messages #4 and #5 ... do you plan to try them also?

 

[arupghoshal]

Thanks Michel,

The formula works like a charm.

Regards,
Arup