Merging columns and adding separators
 

Hi I have 5000+ rows in my sheet the columns are as
follows -

A - Name
B - Unique Reference
C - Address Ln1
D - Address Ln2
E - Address Ln3
F - Address Ln4
G - PostCode

How can I get column H to bring to gether all the address
info into one column and be of the layout like this -

NAME, ADDRESS Ln1, ADDRESS Ln2, ADDRESS Ln3, ADDRESS Ln4,
POSTCODE, UNIQUE REFERENCE

Any help is much appreciated I cannot seem to keep all
the info together using the merge button and I dont know
how to programatically enter the commas to seperate the
results in the single cell.

Pete

Merging columns and adding separators
 

in H1 put in

=A1 & ", " & C1 & ", " & D1 & ", " & E1 & ", " & F1 & ", " & G1 & ", " & B1

than drag fill down column H

select column H and do Edit=Copy then immediately Edit=Paste Special and
select Values.

--
Regards,
Tom Ogilvy

"Pete" wrote in message
...
Hi I have 5000+ rows in my sheet the columns are as
follows -

A - Name
B - Unique Reference
C - Address Ln1
D - Address Ln2
E - Address Ln3
F - Address Ln4
G - PostCode

How can I get column H to bring to gether all the address
info into one column and be of the layout like this -

NAME, ADDRESS Ln1, ADDRESS Ln2, ADDRESS Ln3, ADDRESS Ln4,
POSTCODE, UNIQUE REFERENCE

Any help is much appreciated I cannot seem to keep all
the info together using the merge button and I dont know
how to programatically enter the commas to seperate the
results in the single cell.

Pete

Merging columns and adding separators
 

Thanks that has worked great is there a way to ensure all
spaces other than 1 following a comma should be ommited
as I have great big spaces in the address now!

Merging columns and adding separators
 

=Trim(Substitute(A1 & ", " & C1 & ", " & D1 & ", " & E1 & ", " & F1 & ", " &
G1 & ", " & B1,char(160)," "))

--
Regards,
Tom Ogilvy




"Pete" wrote in message
...
Thanks that has worked great is there a way to ensure all
spaces other than 1 following a comma should be ommited
as I have great big spaces in the address now!

Merging columns and adding separators
 

Thanks Tom


-----Original Message-----
=Trim(Substitute(A1 & ", " & C1 & ", " & D1 & ", " & E1
& ", " & F1 & ", " &
G1 & ", " & B1,char(160)," "))

--
Regards,
Tom Ogilvy




"Pete" wrote in
message
...
Thanks that has worked great is there a way to ensure
all
spaces other than 1 following a comma should be ommited
as I have great big spaces in the address now!


.