Group multiple columns into one

sbawnh

New Member
Joined
Feb 25, 2019
Messages
33
Good morning!
I'm interested in getting a single cell that lists a group of dates originating from multiple columns.

Imagine column A is ID's, Imagine columns(B:F) are dates relating to the ID.

I want to put the dates all together in one cell, but using this formula, if there are blanks, I will have extra Commas.

How else could I do this?

Thank you!

=TEXT(B2,"mm/dd/yyyy")&", "&TEXT(C2,"mm/dd/yyyy")&", "&TEXT(D2,"mm/dd/yyyy")&", "&TEXT(E2,"mm/dd/yyyy")&", "&TEXT(F2,"mm/dd/yyyy")

I tried ideas of concatenation, but I don't like where it's going. Can I use REPT with text, based on count of non-blanks?

=CONCATENATE(IF(B2="","",(TEXT(B2,"mm/dd/yyyy")))&", "&IF(C2="","",TEXT(C2,"mm/dd/yyyy"))) Etc..
 
Last edited:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

sbawnh

New Member
Joined
Feb 25, 2019
Messages
33
I got somewhere! I just have a single end comma now!

=LEFT(TEXT(B2,"mm/dd/yyyy")&", "&TEXT(C2,"mm/dd/yyyy")[ETC...],COUNT(B2:F2)*12)

02/01/2019, 03/01/2019,
 

sbawnh

New Member
Joined
Feb 25, 2019
Messages
33
I'm done! Sorry. You can delete or if it's interesting, thank you for helping if you've seen this.

=LEFT(TEXT(B2,"mm/dd/yyyy")&", "&TEXT(C2,"mm/dd/yyyy")
[ETC...],COUNT(B2:F2)*12),COUNT(S2:Y2)*12-2)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,405
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top