Merging Excel

[ridge]

I have a workbook... 5,000+ entries.
Unfortunanty, the information is broken down as so....
A B C D E F
(name) Address-1 Address-2 City Zip Phone #

I need to merge the address text columns to read like this.

A B
Name Address1
Address2
City
Phone

 

[GlennUK]

For what purpose? You have a structured table, and what you're asking for looks like a printout request, not a table redesign.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[shetlandbob]

dont know how to auto do it without VBA - some of the other folk here might?

In VBA you could insert a new column between A and B
and then loop through your data using the following code:

ActiveCell.FormulaR1C1 = ActiveCell.Offset(0, 1) & Chr(10) & ActiveCell.Offset(0, 2) [green]%etc....... (for all columns)
% Loop for all rows[/green]

where activecell is the cell you want your concatenated address to go

Robert Cumming

http://www.consultantengineer.co.uk

 

[ridge]

Just trying to make it more user friendly... I can print individual mailing lables, etc alot easier...
It would be simply easier to have the complete address
in one complete "wrapped text" cell

 

[AnotherHiggins]

I can print individual mailing lables, etc alot easier.

No, no, no. Just go into word and do a mail merge from a separate data source. And that will be MUCH easier if you leave the data the way it is now - in a proper data table.

If you want to make something that is easier for a user to view, I'd suggest making a report based on the data table. But you definitely want to leave the data stored as it is now.

It will make your job easier in the long run.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[Gavona]

Interesting.
If you type dog [Alt]-[Enter] cat [enter]
then dog and cat are entered into the same cell on different lines.
Using Code(Mid(...)) I find that the newline character is char(10). (yes, I know that bob knew that already)
However, =C11&CHAR(10)&D11 does not work

So in Excel97 at least I would revert to Bob's code - but why can't it be done without code?

Gavin


Gavin

 

[Gavona]

Got it. You can create a simple formula along the lines of my earlier post and copy it down to all relevant rows.

It only works however when you convert it to values.
Select the Column or range,
Edit, Copy
Edit, PasteSpecial, Values

So there is a simple non VBA way.


Gavin