concatenation query

[Joey Jr]

Hi there,
I have a frustrating problem, to which I'm sure there must be a solution.

I have a worksheet which contains customer details. Columns G, H, I, J and K contain various parts of the customers address: Street, District, Town, County and Postcode. In Column L, I need to have the full address, with each part of the address separated by a comma, and a space. I have used the following formula:

L1=concatenate(G1", "H1", "I1", "J1", "K1)

This works fine, provided each customer has all 5 parts of the address. However I have over 27,000 rows of customer details, and some customers addresses don't contain all five parts. This proves to be a problem as the following address:
Street = 1 The Street
District = Blank
Town = London
County = Blank
Postcode = AA1 1ZZ

Appears as

1 The Street, , London, , AA1 1ZZ.

The duplicate commas being caused by the formula inserting the blank cells.

In a nutshell, I need a way to only insert the comma and space after a cell that contains data, and ignore a blank cell.

Any ideas gratefully recieved.

Cheers
Joe

 

[Andrewdm]

Why don't you do a "Find Replace" on your result. ie replace all instances of two commas with one.

Replace , ,
with ,

 

[Joey Jr]

Morning Andrew,
You would have thought that would work, but it cant seem to find any instances of this occuring, although I can plainly see them occuring on the screen. I wondered whether this was because the cells with the duplicates contain the concatenation formula, so , , is actually ,", ?

Does that make any sense?

 

[eliW]

On 2002-11-01 07:02, Joey Jr wrote:
Morning Andrew,
You would have thought that would work, but it cant seem to find any instances of this occuring, although I can plainly see them occuring on the screen. I wondered whether this was because the cells with the duplicates contain the concatenation formula, so , , is actually ,", ?

Does that make any sense?

Copy and PasteSpecial your column of formulas to Values and then you can replace the commas the way Andrew suggested.Eli

 

[eliW]

You could also use a formula of the following formate:

=A1&IF(LEN(B1)>0,", "&B1,B1)&IF(LEN(C1)>0,", "&C1,C1)

and so on for columns D and E

Eli

 

[Aladin Akyurek]

=G1&IF(LEN(G1),",","")&H1&IF(LEN(H1),",","")&I1&IF(LEN(I1),",","")&J1&IF(LEN(J1),",","")&K1

 

[Joey Jr]

[No message]

 

[Joey Jr]

Sweet!
Worked like a charm!
I opted for the Paste Special route, but cheers for the other solutions, as I will use them in future.

Joe