Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging and adding cells | Excel Discussion (Misc queries) | |||
MERGING COLUMNS TO ONE | Excel Discussion (Misc queries) | |||
merging 2 cells and adding a space. | Excel Discussion (Misc queries) | |||
Merging or Adding two .csv files | Excel Programming | |||
Merging or Adding two .csv files | Excel Programming |