Combining cell contents when there is content to combine

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a spreadsheet that has two columns for vendor addresses. Address 1
col. has the street number and name and the Address 2 col. has the suite
number or Apt number, etc. I want to combine the two addresses into one cell
on one line in the cell with a comma and a space seperating the two
addresses. The problem I'm encountering is when there is not a second address
the comma is still appearing. How do I eliminate the comma when there is no
second address?


Thank You,
 
Richard

Ardus has given a worksheet function for a couple of cells.

If you had many cells to combine this could run into some long formulas.

This UDF will do the trick and leave out the comma when a cell or cells in the
range is blank.

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

=ConCatRange(A1:H1)


Gord Dibben MS Excel MVP
 
Back
Top