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,

Say adresse1 is in col C and address2 in col D:
=C1&IF(D1<"",", "&D1,"")

HTH
--
AP

"Richard" a écrit dans le message de
news: ...
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


On Wed, 21 Jun 2006 10:29:03 -0700, Richard
wrote:

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,