word table convert to excel problem with data converstion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hole Alex - 16 Jan 2008 12:41 GMT

Hi,
I’ve got a new massive mailing list that I’ve just inherited (well over 200
MS word pages).
I’m finding it really difficult to manipulate this amount of data in word.
I know the idea would be to put in access but i don't have the licence, so
excel it is.

The problems are caused by the address column. Because the document wasn't
set up correctly it doesn't have separate address line fields.

It’s just one block of text, where each line of the address is on a
different line of that cell.
When I convert the table to text using a delimited field (so I can import it
in excel) it works fine but excel recognises each of the address lines as a
separate row, where as I would like them to be in one cell and then each
record has its own row.

I’m basically left with a system that has just been fudged and now I’m
trying to sort it out without re entering all of the data.

Any ideas will be gratefully received. If anyone needs any further
information then just let me know.

Many thanks for your help on this in advance.

Reply to this Message

Suzanne S. Barnhill - 16 Jan 2008 14:59 GMT

One possible line of attack:

1. Use Find and Replace to replace paragraph or line breaks (whichever are
used in the multiline cells) with a tab character (^t).

2. Use Table | Convert | Table to Text, separating with tabs.

3. Use Table | Convert | Text to Table, separating at tabs.

That operation will get the separate lines into separate columns. If the
addresses have varying numbers of lines, the situation becomes more complex,
however, since corresponding bits will be in different columns (for example,
if there's no company name, then the street address might end up in the
Company Name column).

Another possible approach would be to replace the line or paragraph breaks
with something like @@, then copy/paste into Excel. After merging from
Excel, use F&R to replace @@ with ^l or ^p as needed.

The really frustrating thing is that you can insert a line break in Excel
with Alt+Enter, but Excel's F&R doesn't provide any way (that I know of) to
find or insert these breaks (or any other special characters).

Signature

Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

> Hi,
> I've got a new massive mailing list that I've just inherited (well over
[quoted text clipped - 23 lines]
>
> Many thanks for your help on this in advance.

Reply to this Message

Hole Alex - 16 Jan 2008 17:28 GMT

thanks for that it does work,
just feels like i'm doing it a long way round.
never mind, we've got it to work. thanks for your help.
Alex

> One possible line of attack:
>
[quoted text clipped - 46 lines]
> >
> > Many thanks for your help on this in advance.

Reply to this Message