Concatenate a row or part of

StuartWhi

Board Regular
Joined
Sep 1, 2011
Messages
75
Hi all,

I have been reading quite a few posts on this and haven’t managed to find what will work for me,
- Several post talking about using VBA, but unfortunately as I’m sharing the document so this wouldn't be ideal (many won’t accept macros),
- Several talking about a non-standard functions which runs a plugin (such as “concatIf”) or VBA also not ideal due to the above.

What I would like to do is concatenate a group of cells if they have text in them.
Quick background, arranging an extended family gathering and people are arriving / departing on different dates,
- Have found each instance of a date and using that to place the who arrive on the same date in different columns,
Arival Date / TimePeople 1.0People 1.1People 1.2People 1.3People 1.4People 1.5People 1.6People 1.7People 1.8People 1.9People 1.10
Tue, 24-Mar-15, 0:00Shirl, Steve, Anthony, Nicole, Chloe
Wed, 25-Mar-15, 16:30Lea, BrettNeil, ValDennis, Marie, TobiYol, Kris, Kyan, AddyJill, Barry
Thu, 26-Mar-15, 18:30Pat, SuzanneStuart, El

<tbody>
</tbody>
I would like to use a formula to then concatenate these into a common cell with a “, “ {comma then space} between them.
- I can achieve this by using this formula but it’s very long and ugly (embedded If’s) is there are better way to do this in a formula.

=IF(J5<>"",IF(AK5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5&", "&AJ5&", "&AK5,IF(AJ5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5&", "&AJ5,IF(AI5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5,IF(AH5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5,IF(AG5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5,IF(AF5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5,IF(AE5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5,IF(AD5<>"",AA5&", "&AB5&", "&AC5&", "&AD5,IF(AC5<>"",AA5&", "&AB5&", "&AC5,IF(AB5<>"",AA5&", "&AB5,IF(AA5<>"",AA5,""))))))))))),"")

All I’m doing in the above is repeating the same thing, checking if the cell has anything in it, if it does add it to the cells before it. There are 10 cells it checks for data thus the reason for being so long.

Any ideas would be appreciated.

Thanks,

Stuart.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,970
Office Version
  1. 2016
Platform
  1. Windows
What I would like to do is concatenate a group of cells if they have text in them.
Quick background, arranging an extended family gathering and people are arriving / departing on different dates,
- Have found each instance of a date and using that to place the who arrive on the same date in different columns,
Arival Date / TimePeople 1.0People 1.1People 1.2People 1.3People 1.4People 1.5People 1.6People 1.7People 1.8People 1.9People 1.10
Tue, 24-Mar-15, 0:00Shirl, Steve, Anthony, Nicole, Chloe
Wed, 25-Mar-15, 16:30Lea, BrettNeil, ValDennis, Marie, TobiYol, Kris, Kyan, AddyJill, Barry
Thu, 26-Mar-15, 18:30Pat, SuzanneStuart, El

<tbody>
</tbody>
I would like to use a formula to then concatenate these into a common cell with a “, “ {comma then space} between them.
- I can achieve this by using this formula but it’s very long and ugly (embedded If’s) is there are better way to do this in a formula.

=IF(J5<>"",IF(AK5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5&", "&AJ5&", "&AK5,IF(AJ5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5&", "&AJ5,IF(AI5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5,IF(AH5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5,IF(AG5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5,IF(AF5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5,IF(AE5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5,IF(AD5<>"",AA5&", "&AB5&", "&AC5&", "&AD5,IF(AC5<>"",AA5&", "&AB5&", "&AC5,IF(AB5<>"",AA5&", "&AB5,IF(AA5<>"",AA5,""))))))))))),"")

All I’m doing in the above is repeating the same thing, checking if the cell has anything in it, if it does add it to the cells before it. There are 10 cells it checks for data thus the reason for being so long.
The range AA5:AK5 has 11 cells in it, not 10. This formula is a little simpler than the one you posted...

=MID(IF(AA5="","",", "&AA5)&IF(AB5="","",", "&AB5)&IF(AC5="","",", "&AC5)&IF(AD5="","",", "&AD5)&IF(AE5="","",", "&AE5)&IF(AF5="","",", "&AF5)&IF(AG5="","",", "&AG5)&IF(AH5="","",", "&AH5)&IF(AI5="","",", "&AI5)&IF(AJ5="","",", "&AJ5)&IF(AK5="","",", "&AK5),3,999)

Note that I did not test J5 to see if it contained a value or not because I assumed if J5 had nothing in it, then none of AA5:AK5 would either. If that assumption is not correct, then use this formula instead...

=IF(J5="","",MID(IF(AA5="","",", "&AA5)&IF(AB5="","",", "&AB5)&IF(AC5="","",", "&AC5)&IF(AD5="","",", "&AD5)&IF(AE5="","",", "&AE5)&IF(AF5="","",", "&AF5)&IF(AG5="","",", "&AG5)&IF(AH5="","",", "&AH5)&IF(AI5="","",", "&AI5)&IF(AJ5="","",", "&AJ5)&IF(AK5="","",", "&AK5),3,999))
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

Try,
=SUBSTITUTE(SUBSTITUTE(TRIM(AA1 & " " & AB1 & " " & AC1 & " " & AD1 & " " & AE1 & " " & AF1 & " " & AG1 & " " & AH1 & " " & AI1 & " " & AJ1 & " " & AK1), ",", "")," ",", ")
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,970
Office Version
  1. 2016
Platform
  1. Windows
Try, adjust the cell references to suit
=SUBSTITUTE(SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1 & " " & D1 & " " & E1 & " " & F1 & " " & G1 & " " & H1 & " " & I1 & " " & J1), ",", "")," ",", ")
That will only work if the text being concatenated are single words... if a single cell contains two or more words, your formula will put a comma/space between each of its words.
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634

ADVERTISEMENT

That will only work if the text being concatenated are single words... if a single cell contains two or more words, your formula will put a comma/space between each of its words.

For me the formula in post #5 does what (I think) is expected.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,970
Office Version
  1. 2016
Platform
  1. Windows
For me the formula in post #5 does what (I think) is expected.
Maybe, as long as all names being concatenated are single names; however, I have a friend whose name first name is "Mary Ann" (with the space in the middle)... her name would come out "Mary, Ann" in the list and look like two people within the overall concatenated list.
 

StuartWhi

Board Regular
Joined
Sep 1, 2011
Messages
75
Hi Gaz and Rick,

Thanks for all your help, I have checked both formulas in my situation and end up with Rick's. The format of the names is "Shirl, Steve, x" as these people are traveling under the same booking. Unfortunately it's not a single person per booking.
In any case both address the task slightly differently and work great in their own way.

Always amazes me what you guys are capable of, take it easy and enjoy the rest of your weekend wherever you are.

Regards,

Stuart.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
You're welcome,
Both should produce the same result, except if there are 1st 2nd names (or surnames), as Rick points out (e.g. Rick Rothstein with my formula would result in Rick, Rothstein, )
 

Watch MrExcel Video

Forum statistics

Threads
1,129,418
Messages
5,636,158
Members
416,903
Latest member
Sanjayaranj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top