If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate
When using concatenate if I have cells that are empty in
that row how can I disregard including the empty cell, without typing function in manually for each row ? Example: Col 1 Col 2 Col 3 Col 4 Col 5 Dan Dave Paul Sue Function I use is =CONCATENATE(A1,",",B1,",",C1,",",E1,",",) Results I receive a Dan,,Dave,Paul, Sue Like to see: Dan, Dave, Paul, Sue |
#2
|
|||
|
|||
Try in say, F1:
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ") Copy F1 down The above essentially uses TRIM to remove any extraneous "in-between" spaces from the concat string first, then SUBSTITUTE will replace all the single spaces: " " in between words with a comma-space: ", " to give the desired result -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- "JT" wrote in message ... When using concatenate if I have cells that are empty in that row how can I disregard including the empty cell, without typing function in manually for each row ? Example: Col 1 Col 2 Col 3 Col 4 Col 5 Dan Dave Paul Sue Function I use is =CONCATENATE(A1,",",B1,",",C1,",",E1,",",) Results I receive a Dan,,Dave,Paul, Sue Like to see: Dan, Dave, Paul, Sue |
#3
|
|||
|
|||
Hi!
Try this: =SUBSTITUTE(CONCATENATE (A1,",",B1,",",C1,",",D1,",",E1),",,",",") Biff -----Original Message----- When using concatenate if I have cells that are empty in that row how can I disregard including the empty cell, without typing function in manually for each row ? Example: Col 1 Col 2 Col 3 Col 4 Col 5 Dan Dave Paul Sue Function I use is =CONCATENATE(A1,",",B1,",",C1,",",E1,",",) Results I receive a Dan,,Dave,Paul, Sue Like to see: Dan, Dave, Paul, Sue . |
#4
|
|||
|
|||
Hi!
Upon further testing I discovered that my formula only works under certain conditions. Use Max's formula, it's more robust. Biff -----Original Message----- Hi! Try this: =SUBSTITUTE(CONCATENATE (A1,",",B1,",",C1,",",D1,",",E1),",,",",") Biff -----Original Message----- When using concatenate if I have cells that are empty in that row how can I disregard including the empty cell, without typing function in manually for each row ? Example: Col 1 Col 2 Col 3 Col 4 Col 5 Dan Dave Paul Sue Function I use is =CONCATENATE(A1,",",B1,",",C1,",",E1,",",) Results I receive a Dan,,Dave,Paul, Sue Like to see: Dan, Dave, Paul, Sue . . |
#5
|
|||
|
|||
JT
In addition to Max's solution you could use a User Defined Function that ignores blank cells. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "," Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function usage is =ConCatRange(A1:E1) which will ignore D1 Gord Dibben Excel MVP On Fri, 25 Feb 2005 19:27:02 -0800, "JT" wrote: When using concatenate if I have cells that are empty in that row how can I disregard including the empty cell, without typing function in manually for each row ? Example: Col 1 Col 2 Col 3 Col 4 Col 5 Dan Dave Paul Sue Function I use is =CONCATENATE(A1,",",B1,",",C1,",",E1,",",) Results I receive a Dan,,Dave,Paul, Sue Like to see: Dan, Dave, Paul, Sue |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Concatenate multiple docs into one and maintain original headers/footers | Mark Parent | General Discussion | 1 | September 30th, 2004 01:18 PM |
Concatenate | Crystal | Worksheet Functions | 2 | February 26th, 2004 04:33 AM |
Concatenate Conditions | Harlan Grove | Worksheet Functions | 0 | January 22nd, 2004 12:21 AM |
Date formats & Concatenate function | Sue | Worksheet Functions | 2 | November 30th, 2003 04:43 AM |