Join text in excel

icytuvi

New Member
Joined
Feb 2, 2020
Messages
30
Office Version
  1. 2013
Platform
  1. Windows
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
BCDEFGHIJ
2DatabaseSummary
3Product 1Product 2Product 3Product 4Product 5Product 6Product 1Alabama, Florida, , , ,
4AlabamaHawaiiIowaWashington DC.Product 2Hawaii, Colorado, , , ,
5FloridaColoradoHawaiiProduct 3Iowa, , , , ,
6ColoradoProduct 4, , , , ,
7IowaProduct 5, , , , ,
8MarylandProduct 6Washington DC., Hawaii, Colorado, Iowa, Maryland, Oregon
9Oregon
Sheet1
Cell Formulas
RangeFormula
J3J3=B4&", "&B5&", "&B6&", "&B7&", "&B8&", "&B9
J4J4=C4&", "&C5&", "&C6&", "&C7&", "&C8&", "&C9
J5J5=D4&", "&D5&", "&D6&", "&D7&", "&D8&", "&D9
J6J6=E4&", "&E5&", "&E6&", "&E7&", "&E8&", "&E9
J7J7=F4&", "&F5&", "&F6&", "&F7&", "&F8&", "&F9
J8J8=G4&", "&G5&", "&G6&", "&G7&", "&G8&", "&G9
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

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