Join text in excel

[icytuvi]

Hi,

I have two tables including the 'Database' and 'Summary' as below. In the 'Summary table' I would like to list out all available value in 'Database' and I am using &", " to join text from the cells.

Could you please help me an alternative formula so that there won't be the comma if the cell is blank? In my version of excel, CONCAT is not available.

Thanks a lot.

Book1.xlsx
	B	C	D	E	F	G	H	I	J
2	Database							Summary
3	Product 1	Product 2	Product 3	Product 4	Product 5	Product 6		Product 1	Alabama, Florida, , , ,
4	Alabama	Hawaii	Iowa			Washington DC.		Product 2	Hawaii, Colorado, , , ,
5	Florida	Colorado				Hawaii		Product 3	Iowa, , , , ,
6						Colorado		Product 4	, , , , ,
7						Iowa		Product 5	, , , , ,
8						Maryland		Product 6	Washington DC., Hawaii, Colorado, Iowa, Maryland, Oregon
9						Oregon
Sheet1

Cell Formulas
Range	Formula
J3	J3	=B4&", "&B5&", "&B6&", "&B7&", "&B8&", "&B9
J4	J4	=C4&", "&C5&", "&C6&", "&C7&", "&C8&", "&C9
J5	J5	=D4&", "&D5&", "&D6&", "&D7&", "&D8&", "&D9
J6	J6	=E4&", "&E5&", "&E6&", "&E7&", "&E8&", "&E9
J7	J7	=F4&", "&F5&", "&F6&", "&F7&", "&F8&", "&F9
J8	J8	=G4&", "&G5&", "&G6&", "&G7&", "&G8&", "&G9

 

[Fluff]

I am using a version of MS Office older than yours and the =CONCATENATE function is definitely available.

The OP said that Concat was not available (which it isn't) not Concatenate.

 

[Burrgogi]

The OP said that Concat was not available (which it isn't) not Concatenate.

Didn't realize that. Thanks.

 

[icytuvi]

OP, I have been waiting to post this because I wanted you to get your answer first without distraction. I have to say that I love your forum avatar. Is he/she yours? He's very cute!

I have been a lifelong dog lover. A few years ago, I had a new neighbor move in next door to me. I got to know him and his family. They had an orange cat with a cute little personality. I never liked cats to begin with but he completely won me over. I'm officially a dog AND a cat person now. So much so, that I plan on adopting a cat someday. I miss Duke (cat) a lot!



By the way, I am using a version of MS Office older than yours and the =CONCATENATE function is definitely available. I suspect you have it as well. You probably just missed it.

Wow it's so lovely to read your comment :D Yes the cat is mine and he's over 8 years old now, he's super adorable and clingy. I also used to prefer dog to cat but my ex prefer cat so we decided to adopt cat. At first we intended to look for a female one but he won my heart with his chubby look of a 2 months old kitten, so we adopted him and a female white cat. His mate is cooler and naughtier while he's clingy and soft :D If you love cat, don't wait any longer to adopt one to experience every moment with him/her :D

Yeah before posting this thread, I tried looking for answers in Google and one of the solution I found was CONCAT(not CONCATENATE which is also available in my excel version). It's just that I don't know how to use it with condition so I came here for help and as always I received a lot of heartful and useful support from everyone here.

 

[Burrgogi]

What is your cat's name? The look on his face and the pose he is holding in that picture is adorable. I would love to meet him and give him tons of pets!! Seriously!

It's kind of odd that he is clingy. Most cats I have met (with the exception of Duke & another cat that I know of) are the opposite. Duke wasn't exactly the "clingy" type but he definitely had his affectionate moments. I would absolutely love to get a cat like yours or Duke someday. Unfortunately, it's not the right time for me - I will have to wait a little while longer. But when I am ready, I hope the cat gods will smile upon me and bless me with an affectionate cat like yours.

 

[icytuvi]

What is your cat's name? The look on his face and the pose he is holding in that picture is adorable. I would love to meet him and give him tons of pets!! Seriously!

It's kind of odd that he is clingy. Most cats I have met (with the exception of Duke & another cat that I know of) are the opposite. Duke wasn't exactly the "clingy" type but he definitely had his affectionate moments. I would absolutely love to get a cat like yours or Duke someday. Unfortunately, it's not the right time for me - I will have to wait a little while longer. But when I am ready, I hope the cat gods will smile upon me and bless me with an affectionate cat like yours.

Duke must be your former cat right? And you must had many beautiful memories with him My cat's name is Tep - meaning baby shrimp in Vietnamese. If you ever have chance to come to Vietnam, and he's still around, we'd welcome you to meet him :D If you use facebook, you'll see some photos of him if you search for tepbongtom4ever. Tep is that type of cat who loves to be paid attention to and cared of, and who'd make you not wanting to go to work but staying with him all day caressing his fur, rubbing his neck and patting his head. His favorite sleeping position is laying his head and neck on your thigh/leg. Wishing that you'll have a super lovely cat like that when you are ready :D

@forum admin: the problem in this thread was effectively solved so pls forgive us the cat lovers for this conversation

 

[Burrgogi]

Tep is a very cute name! We can continue our conversation through PM feature here. Check in your inbox.