How do I import "manual linefeeds" into Excel?

[Greg]

Hi,

I have created an MS Word 2003 document that contains "manual linefeed"
and paragraph marks. Each new record is separated by 2 paragraph marks.
How should I save this document such that when it is imported into MS
Excel, it will keep each record separated? Currently, when I import
the file it makes a new record each time a "manual linefeed" is found.

Thanks in advance for any assistance.

-Greg

 

[Dave Peterson]

Are each of the fields on the record separated by that manual linefeed? Or is
it just one giant field per record?

If it's multiple fields separated by manual linefeeds, then I'd change those
manual linefeeds to a symbol that isn't used in your data--say a vertical bar
(|).

There's an option in MSWord's Edit|Replace dialog.

Click on More if you don't see the extended dialog.
then click on Special and choose Manual Line break

Then do the same thing with the double paragraph marks--change it to a single
paragraph mark.

Now copy and paste into excel.

Your data should go into a single column.
Data|text to columns|Delimited by that vertical bar
and choose the type of data for each field.

And then back to MSWord. Close without saving or hit Undo a bunch of times.

 

[Greg]

Hi Dave,

I appreciate your reply. After changing the "manual line feeds" into
"horizontal bars (|)" and pasting my records into Excel things are just
about the way I want them. Next I need the "horizonal bars (|)"
converted back into "manual linefeeds".

I tried to do a global "Find and Replace" but I guess I'm missing
something as Excel doesn't recognize the (^10) or (^l) characters. Do
you have any suggestions on how to resolve this minor issue?

-Greg

 

[Chip Pearson]

In the Replace dialog, enter | in the Find What box, put your cursor in the
Replace With box, hold down the left ALT key and type 0010 on the numeric
keypad (not the number keys above the letters). Nothing will show in the
box, but the vbLf character will be there. Then click Replace All to
replace.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

Hi Dave,

I appreciate your reply. After changing the "manual line feeds" into
"horizontal bars (|)" and pasting my records into Excel things are just
about the way I want them. Next I need the "horizonal bars (|)"
converted back into "manual linefeeds".

I tried to do a global "Find and Replace" but I guess I'm missing
something as Excel doesn't recognize the (^10) or (^l) characters. Do
you have any suggestions on how to resolve this minor issue?

-Greg

 

[Greg]

Thanks Chip. That is just what I needed.

In the Replace dialog, enter | in the Find What box, put your cursor in the
Replace With box, hold down the left ALT key and type 0010 on the numeric
keypad (not the number keys above the letters). Nothing will show in the
box, but the vbLf character will be there. Then click Replace All to
replace.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)