I have a word file, with a table of addresses. When I try to paste the table into Excel, the cells split into twos and threes, frustrating my efforts to run a merge from it. Is there any way to paste it into Excel and keep the table shape? It would be a pain to copy and paste 292 times from the Word table to Word labels :smack:
Well, it depends. If you have carriage returns in your Word Table, excel will not know what to do with those, since it normally sees a CR as the stopping point of a cell. I assume you have a “table” of address labels, right? Each label is formatted like:
Name
Street
City, State Zip
right?
There’s not going to be an easy way for you to get that into Excel without some data manipulation. In Excel, what you will need to do is format it like this (with | indicating a new cell)
Name | Street | City | State | Zip
Also, they will need to be horizontal, as opposed to vertical (ie each address should make up a row, not a column.
What I would do it copy one column of your label table into excel. Create a macro using “relative references” that moves the fields around, and keep running it until all the data from the first column of your labels is converted. Then do the next column, etc etc.
It’s still going to be more work than a simple copy/paste, but it won’t be 292 copy and paste steps.
The way the data appears is in a table 3 columns wide by many deep. In the right hand cell, there are 3-4 rows of address, that’s the problem as I see it.
Pushkin, can you post an excerpt of that file somewhere so we can take a look?
If the data is confidential or sensitive, could you create a Word doc with made-up examples that use the same format?
You can email it to me (cannotreply@gmail.com) if you can’t post it anywhere.
Yeah, you got it. More specifically, it’s the line breaks (paragraph marks, if you prefer) that are doing it.
I tried the following. It didn’t work, but maybe it will give someone some ideas.
First, while still in Word, do a Find-and-Replace, looking for ^p (that’s a carat [upper-case 6] followed by the letter “p”) and replacing with some other character (I usually use a tilde, the ~ in the top left corner of the keyboard). This will change all your paragraph marks to tildes, compressing those addresses into a single row each. Now you can copy your table to Excel, and you’ll have the correct number of rows. The problem is that your addresses will be messed up, because Excel’s find-and-replace can’t handle special characters. Oh well, sorry. 
Amazing how difficult this is, maybe Bill Gates should grab his Excel people and take them down the hall and introduce them to his Word people.
Keeve, picking up where you left off…
In Word do the search and replace with the tilde. Paste in Excel and run the following macro against the selected range:
Sub Zap()
Dim C As Range, CellText As String, x As Integer
For Each C In Selection
CellText = C.Text
x = InStr(CellText, "~")
Do While x <> 0
CellText = Mid(CellText, 1, x - 1) & vbLf & Mid(CellText, x + 1)
x = InStr(CellText, "~")
Loop
C.Value = CellText
Next
End Sub
Basically I have my addresses in cells in a column like so;
David McBlah,
15 Blah Gardens,
Blah,
Blah Postcode
And sometimes there’s no “Blah and Postcode”, so I’ve a mixture of three and four line addresses.
Typical academics, they give me this at the last minute with some fracked up formatting :rolleyes: As soon as I suggested inputting addresses into Excel to make it easy for me to run a merge, I was told they were already in a Word table :smack:
I’ll try what’s been suggested, thanks all.
Pushkin, sorry I wasn’t clear… I asked for an excerpt in order to see if some sort of data manipulation could be done with a third-party program, or if a macro could be made to programatically and automatically do the work for ya. Something as close to the actual data as possible would be best, because the inconsistencies are the most important part.
K364 already made you a macro, I see. Does that work?
You mean the table only has one column, and the data is separated by rows? Like this example?
Close. Based on post #3, I’d say that he has three columns, the third of which is exactly like you have it, but the first two are normal with no line breaks.
On closer look, actually, I think the suggestions here already suffice. Oops 