Concatenate Multiple Cells with Blanks (no VBA)

1

1313

New Member
Joined
Dec 8, 2011
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have read other threads and I cannot find one that quite matches our needs.....

We have 17 columns of data that need to be concatenated - one row of 17 into one cell.

Some of the cells will have data, some will not. Some are static, meaning it will be the same data set in each cell (e.g. Current Parent). Some cells will have a different numeric number (that can vary between rows).

I have tried: =SUBSTITUTE(TRIM(IF(V2="Alumni",V$2&" ","")&IF(W2=" ",W$2&" ","")&IF(X2="Current Parent",X$2,"")&IF(Y2="<>",Y$2&" ","")&IF(Z2="<>",Z$2&" ","")&IF(AA2="Current Parent",AA$2,"")&IF(AB2="<>",AB$2&" ","")&IF(AC2="Faculty/Staff",AC$2,"")&IF(AD2="Grandparent",AD$2&" ","")&IF(AE2="Former Fac/Staff",AE$2&" ","")&IF(AF2="<>",AG$2,""))," ",", ")

And I am sure I have written this incorrect... it doesn't return all data sets, nor do it capture the numeric data.

Need to concatenate columns V through AL, formula in AM.

Book2
VWXYZAAABACADAEAFAGAHAIAJAKALAM
1AlumniClass YearCurrent ParentChild's Class YearChild's Class Year_1Child's Class Year_2Child's Class Year_3Fac/StaffGrandparentParent of AlumniFormer Fac/StaffCons CodesCons Codes_1Cons Codes_2FY22 PF TotalFY22 Non PFFY22 Total GivingTags
2Alumni1996Current Parent202420222034Faculty/StaffFY22PFFY22GIV
3Alumni1989Current Parent20252028Faculty/Staff
4Alumni1991Current Parent20292026Faculty/Staff
5Alumni1997Current Parent20322028Faculty/StaffFY22NONPFFY22GIV
6Alumni1992Current Parent20272023Faculty/StaffFY22NONPFFY22GIV
7Alumni1999Current Parent20332028Faculty/StaffFY22NONPFFY22GIV
8Alumni1990Current Parent20262024Faculty/StaffFY22NONPFFY22GIV
9Alumni1998Current Parent20302028Faculty/StaffFY22NONPFFY22GIV
10Alumni1989Current Parent20302028Faculty/StaffFY22NONPFFY22GIV
11Alumni1999Current Parent20312033Faculty/StaffFY22NONPFFY22GIV
12Alumni2005Current Parent20342032Faculty/StaffFY22PFFY22GIV
13Alumni1997Current Parent20252027Faculty/StaffFY22PFFY22GIV
Sheet1
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Hi,

Since I don't have 365, this is one way:

Book3.xlsx
VWXYZAAABACADAEAFAGAHAIAJAKALAM
1AlumniClass YearCurrent ParentChild's Class YearChild's Class Year_1Child's Class Year_2Child's Class Year_3Fac/StaffGrandparentParent of AlumniFormer Fac/StaffCons CodesCons Codes_1Cons Codes_2FY22 PF TotalFY22 Non PFFY22 Total GivingTags
2Alumni1996Current Parent202420222034Faculty/StaffFY22PFFY22GIVAlumni,1996,Current Parent,2024,2022,2034,Faculty/Staff,FY22PF,FY22GIV
3Alumni1989Current Parent20252028Faculty/StaffAlumni,1989,Current Parent,2025,2028,Faculty/Staff
4Alumni1991Current Parent20292026Faculty/StaffAlumni,1991,Current Parent,2029,2026,Faculty/Staff
5Alumni1997Current Parent20322028Faculty/StaffFY22NONPFFY22GIVAlumni,1997,Current Parent,2032,2028,Faculty/Staff,FY22NONPF,FY22GIV
6Alumni1992Current Parent20272023Faculty/StaffFY22NONPFFY22GIVAlumni,1992,Current Parent,2027,2023,Faculty/Staff,FY22NONPF,FY22GIV
7Alumni1999Current Parent20332028Faculty/StaffFY22NONPFFY22GIVAlumni,1999,Current Parent,2033,2028,Faculty/Staff,FY22NONPF,FY22GIV
8Alumni1990Current Parent20262024Faculty/StaffFY22NONPFFY22GIVAlumni,1990,Current Parent,2026,2024,Faculty/Staff,FY22NONPF,FY22GIV
9Alumni1998Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni,1998,Current Parent,2030,2028,Faculty/Staff,FY22NONPF,FY22GIV
10Alumni1989Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni,1989,Current Parent,2030,2028,Faculty/Staff,FY22NONPF,FY22GIV
11Alumni1999Current Parent20312033Faculty/StaffFY22NONPFFY22GIVAlumni,1999,Current Parent,2031,2033,Faculty/Staff,FY22NONPF,FY22GIV
12Alumni2005Current Parent20342032Faculty/StaffFY22PFFY22GIVAlumni,2005,Current Parent,2034,2032,Faculty/Staff,FY22PF,FY22GIV
13Alumni1997Current Parent20252027Faculty/StaffFY22PFFY22GIVAlumni,1997,Current Parent,2025,2027,Faculty/Staff,FY22PF,FY22GIV
Sheet1051
Cell Formulas
RangeFormula
AM2:AM13AM2=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(V2&","&W2&","&X2&","&Y2&","&Z2&","&AA2&","&AB2&","&AC2&","&AD2&","&AE2&","&AF2&","&AG2&","&AH2&","&AI2&","&AJ2&","&AK2&","&AL2," ","|"),","," "))," ",","),"|"," ")


Since You have 365, with TEXTJOIN, the formula can be much shorter.

Your TEXTJOIN formula will look something like this:

Excel Formula: 
=TEXTJOIN(",",TRUE,V2:AL2)

Untested, because I don't have 365
 
Last edited:
Upvote 0
Solution
As I understand your question, I believe that @jtakw's TEXTJOIN formula is what you want, other than, after looking at your original formula attempt, I think you want a comma and space between the terms.
Here it is in action. (I have hidden some of the blank columns to keep the mini-sheet a bit smaller)

22 03 12.xlsm
VWXYZAAABACADAJAKALAM
1AlumniClass YearCurrent ParentChild's Class YearChild's Class Year_1Child's Class Year_2Child's Class Year_3Fac/StaffGrandparentFY22 PF TotalFY22 Non PFFY22 Total GivingTags
2Alumni1996Current Parent202420222034Faculty/StaffFY22PFFY22GIVAlumni, 1996, Current Parent, 2024, 2022, 2034, Faculty/Staff, FY22PF, FY22GIV
3Alumni1989Current Parent20252028Faculty/StaffAlumni, 1989, Current Parent, 2025, 2028, Faculty/Staff
4Alumni1991Current Parent20292026Faculty/StaffAlumni, 1991, Current Parent, 2029, 2026, Faculty/Staff
5Alumni1997Current Parent20322028Faculty/StaffFY22NONPFFY22GIVAlumni, 1997, Current Parent, 2032, 2028, Faculty/Staff, FY22NONPF, FY22GIV
6Alumni1992Current Parent20272023Faculty/StaffFY22NONPFFY22GIVAlumni, 1992, Current Parent, 2027, 2023, Faculty/Staff, FY22NONPF, FY22GIV
7Alumni1999Current Parent20332028Faculty/StaffFY22NONPFFY22GIVAlumni, 1999, Current Parent, 2033, 2028, Faculty/Staff, FY22NONPF, FY22GIV
8Alumni1990Current Parent20262024Faculty/StaffFY22NONPFFY22GIVAlumni, 1990, Current Parent, 2026, 2024, Faculty/Staff, FY22NONPF, FY22GIV
9Alumni1998Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni, 1998, Current Parent, 2030, 2028, Faculty/Staff, FY22NONPF, FY22GIV
10Alumni1989Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni, 1989, Current Parent, 2030, 2028, Faculty/Staff, FY22NONPF, FY22GIV
11Alumni1999Current Parent20312033Faculty/StaffFY22NONPFFY22GIVAlumni, 1999, Current Parent, 2031, 2033, Faculty/Staff, FY22NONPF, FY22GIV
12Alumni2005Current Parent20342032Faculty/StaffFY22PFFY22GIVAlumni, 2005, Current Parent, 2034, 2032, Faculty/Staff, FY22PF, FY22GIV
13Alumni1997Current Parent20252027Faculty/StaffFY22PFFY22GIVAlumni, 1997, Current Parent, 2025, 2027, Faculty/Staff, FY22PF, FY22GIV
TEXTJOIN
Cell Formulas
RangeFormula
AM2:AM13AM2=TEXTJOIN(", ",TRUE,V2:AL2)


If this is not what you want, please post the mini-sheet again after you have manually typed in the results that you want in column AM.
 
Upvote 0
jtakw said:
Hi,

Since I don't have 365, this is one way:

Book3.xlsx
VWXYZAAABACADAEAFAGAHAIAJAKALAM
1AlumniClass YearCurrent ParentChild's Class YearChild's Class Year_1Child's Class Year_2Child's Class Year_3Fac/StaffGrandparentParent of AlumniFormer Fac/StaffCons CodesCons Codes_1Cons Codes_2FY22 PF TotalFY22 Non PFFY22 Total GivingTags
2Alumni1996Current Parent202420222034Faculty/StaffFY22PFFY22GIVAlumni,1996,Current Parent,2024,2022,2034,Faculty/Staff,FY22PF,FY22GIV
3Alumni1989Current Parent20252028Faculty/StaffAlumni,1989,Current Parent,2025,2028,Faculty/Staff
4Alumni1991Current Parent20292026Faculty/StaffAlumni,1991,Current Parent,2029,2026,Faculty/Staff
5Alumni1997Current Parent20322028Faculty/StaffFY22NONPFFY22GIVAlumni,1997,Current Parent,2032,2028,Faculty/Staff,FY22NONPF,FY22GIV
6Alumni1992Current Parent20272023Faculty/StaffFY22NONPFFY22GIVAlumni,1992,Current Parent,2027,2023,Faculty/Staff,FY22NONPF,FY22GIV
7Alumni1999Current Parent20332028Faculty/StaffFY22NONPFFY22GIVAlumni,1999,Current Parent,2033,2028,Faculty/Staff,FY22NONPF,FY22GIV
8Alumni1990Current Parent20262024Faculty/StaffFY22NONPFFY22GIVAlumni,1990,Current Parent,2026,2024,Faculty/Staff,FY22NONPF,FY22GIV
9Alumni1998Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni,1998,Current Parent,2030,2028,Faculty/Staff,FY22NONPF,FY22GIV
10Alumni1989Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni,1989,Current Parent,2030,2028,Faculty/Staff,FY22NONPF,FY22GIV
11Alumni1999Current Parent20312033Faculty/StaffFY22NONPFFY22GIVAlumni,1999,Current Parent,2031,2033,Faculty/Staff,FY22NONPF,FY22GIV
12Alumni2005Current Parent20342032Faculty/StaffFY22PFFY22GIVAlumni,2005,Current Parent,2034,2032,Faculty/Staff,FY22PF,FY22GIV
13Alumni1997Current Parent20252027Faculty/StaffFY22PFFY22GIVAlumni,1997,Current Parent,2025,2027,Faculty/Staff,FY22PF,FY22GIV
Sheet1051
Cell Formulas
RangeFormula
AM2:AM13AM2=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(V2&","&W2&","&X2&","&Y2&","&Z2&","&AA2&","&AB2&","&AC2&","&AD2&","&AE2&","&AF2&","&AG2&","&AH2&","&AI2&","&AJ2&","&AK2&","&AL2," ","|"),","," "))," ",","),"|"," ")


Since You have 365, with TEXTJOIN, the formula can be much shorter.

Your TEXTJOIN formula will look something like this:

Excel Formula: 
=TEXTJOIN(",",TRUE,V2:AL2)

Untested, because I don't have 365
Click to expand...
THANK YOU! This is perfect! Appreciate your time!
 
Upvote 0
Peter_SSs said:
As I understand your question, I believe that @jtakw's TEXTJOIN formula is what you want, other than, after looking at your original formula attempt, I think you want a comma and space between the terms.
Here it is in action. (I have hidden some of the blank columns to keep the mini-sheet a bit smaller)

22 03 12.xlsm
VWXYZAAABACADAJAKALAM
1AlumniClass YearCurrent ParentChild's Class YearChild's Class Year_1Child's Class Year_2Child's Class Year_3Fac/StaffGrandparentFY22 PF TotalFY22 Non PFFY22 Total GivingTags
2Alumni1996Current Parent202420222034Faculty/StaffFY22PFFY22GIVAlumni, 1996, Current Parent, 2024, 2022, 2034, Faculty/Staff, FY22PF, FY22GIV
3Alumni1989Current Parent20252028Faculty/StaffAlumni, 1989, Current Parent, 2025, 2028, Faculty/Staff
4Alumni1991Current Parent20292026Faculty/StaffAlumni, 1991, Current Parent, 2029, 2026, Faculty/Staff
5Alumni1997Current Parent20322028Faculty/StaffFY22NONPFFY22GIVAlumni, 1997, Current Parent, 2032, 2028, Faculty/Staff, FY22NONPF, FY22GIV
6Alumni1992Current Parent20272023Faculty/StaffFY22NONPFFY22GIVAlumni, 1992, Current Parent, 2027, 2023, Faculty/Staff, FY22NONPF, FY22GIV
7Alumni1999Current Parent20332028Faculty/StaffFY22NONPFFY22GIVAlumni, 1999, Current Parent, 2033, 2028, Faculty/Staff, FY22NONPF, FY22GIV
8Alumni1990Current Parent20262024Faculty/StaffFY22NONPFFY22GIVAlumni, 1990, Current Parent, 2026, 2024, Faculty/Staff, FY22NONPF, FY22GIV
9Alumni1998Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni, 1998, Current Parent, 2030, 2028, Faculty/Staff, FY22NONPF, FY22GIV
10Alumni1989Current Parent20302028Faculty/StaffFY22NONPFFY22GIVAlumni, 1989, Current Parent, 2030, 2028, Faculty/Staff, FY22NONPF, FY22GIV
11Alumni1999Current Parent20312033Faculty/StaffFY22NONPFFY22GIVAlumni, 1999, Current Parent, 2031, 2033, Faculty/Staff, FY22NONPF, FY22GIV
12Alumni2005Current Parent20342032Faculty/StaffFY22PFFY22GIVAlumni, 2005, Current Parent, 2034, 2032, Faculty/Staff, FY22PF, FY22GIV
13Alumni1997Current Parent20252027Faculty/StaffFY22PFFY22GIVAlumni, 1997, Current Parent, 2025, 2027, Faculty/Staff, FY22PF, FY22GIV
TEXTJOIN
Cell Formulas
RangeFormula
AM2:AM13AM2=TEXTJOIN(", ",TRUE,V2:AL2)


If this is not what you want, please post the mini-sheet again after you have manually typed in the results that you want in column AM.
Click to expand...
Thank you SO much! The solution is prefect - exactly what we needed. Thank you so much!
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0
You're welcome, thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Ignore Empty Cells?! Help!
Replies
3
Views
469
Fluff
Fluff
D
  • Solved
Providing message if red coloured cells found in specific columns
2
Replies
10
Views
692
NdNoviceHlp
NdNoviceHlp
M
  • Question
Dynamic Cell Editing from Another Sheet
Replies
3
Views
187
Cubist
Cubist
J
  • Solved
Excel Selenium VBA Run-time error '32'
Replies
5
Views
691
Js Smith
J
T
  • Question
Hide and unhide rows based on empty cells
Replies
0
Views
838
The_Steward
T

Forum statistics

Threads
1,216,135
Messages
6,129,075
Members
449,485
Latest member
greggy

Share this page

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