Combine multiple cells text into one cell, skip blank cells.

**T15K** · 01-07-2014, 10:35 PM

Hello,
I need help with a formula to join multiple text from different columns into one.
For example, combine text in A1, B1, C1, and D1 into a cell E1. I would like each cell to be separated by a comma
so far in E1 I have A1&","&B1&","C1&","&D1

The complication is that sometimes A to D might be blank, and if that is the case I dont want that blank to be copied across. At present I would end up with a double comma in the cases where there is no data in any cell.

Can anyone help me with this problem?

Thanks in advance
T15K

**rcm** · 01-07-2014, 10:40 PM

Please Login or Register  to view this content.

**martindwilson** · 01-07-2014, 10:46 PM

if the cells have text without spaces in each cell then

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1)," ",",")
if the cells contain more than one word each then
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ","^")&" "&SUBSTITUTE(B1," ","^")&" "&SUBSTITUTE(C1," ","^")&" "&SUBSTITUTE(D1," ","^"))," ",","),"^"," ")

**T15K** · 01-08-2014, 03:17 AM

Both great solutions, thank you very much.

**mfaheem1234** · 12-05-2014, 09:42 AM

Hello everyone,
Does anybody have an idea what to do if we have a large number of cells (80 or 90)? Is there any VBA solution for that?

Thanks

**CHatmaker** · 12-05-2014, 10:04 AM

Please Login or Register  to view this content.

**protonLeah** · 12-05-2014, 09:49 PM

mfaheem1234,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Combine multiple cells text into one cell, skip blank cells.

LinkBack

Thread Tools

Rate This Thread

Display

Combine multiple cells text into one cell, skip blank cells.

Re: Combine multiple cells text into one cell, skip blank cells.

Re: Combine multiple cells text into one cell, skip blank cells.

Re: Combine multiple cells text into one cell, skip blank cells.

Re: Combine multiple cells text into one cell, skip blank cells.

Re: Combine multiple cells text into one cell, skip blank cells.

Re: Combine multiple cells text into one cell, skip blank cells.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to make Excel VBA to skip blank cells until a cell has a number

[SOLVED] Copy text from multiple cells and combine to one cell

Combine text in multiple cells if adjacent column is blank

Excel 2007 : Combine Text From Multiple Cells Into New Cell Based On Criteria

Combine text from multiple cells, but not when cell is blank

Bookmarks

Bookmarks

Posting Permissions