import long Word document

[Guest]

Using Office 2003.
Daily we dictate patients notes and the secretary creates a long Word
document including the ~10 patient as one long file. (This makes it easier
for the docs to edit.) Each patient note is ~3pages w/hard page breaks
between patients.

I now have a need to upload these files into an Electronic Medical Record
system but I need to break out the separate pt notes into Excel cells, one
patient note per cell.

When I Copy|Paste the entire document each hard paragraph puts text into a
separate cell. Is there a way to massage the data quickly to place say 10 pt
notes into 10 vertical cells? I've tried "Replacing" hard paragraphs with
unique text but I can't reverse the process in Excel to replace the hard
paragraphs.

Does Office 2007 have a better feature for this?

Thanks,
philr

 

[Guest]

I've read several other posts in this forum where the recommendation is to
place random characters like $$$$$, but their solutions don't seem to work
for me. In the Replace with box I've tried alt 0010, alt 0182, alt 0160 (all
on the number pad) ctrl-j, ctrl+j, ctrlj but each time I get an error saying
"formula too long." The alt 0182 shows the hard paragraph symbol but I get
the same error message. The alt 0160 shows a period.

Thx,
philr

 

[Dave Peterson]

I think you're getting hit by two problems.

This has always worked for me to put text copied from MSWord into excel (not as
single cell/per paragraph):

If you're copying everything to a single cell in excel, just paste into the
formula bar. (A variation of Southpaw's suggestion.)

If you're copying from an MSWord table with each of those cells having multiple
paragraphs/linebreaks...

Saved from a previous post:

If your cells in your word table contain paragraph mark or linebreak characters,
then excel will bring them over as separate cells.

One way around it is to convert those paragraph marks & linebreaks to unique
characters, then copy|paste and then convert them back to linefeeds.

I like this technique (inside a copy of the word file):
Select your table.
Edit|replace|Special (show More if required)
Find what: (paragraph mark under Special button)
replace with: $$$$$ (if $$$$$ doesn't appear in the table)
replace all

Same thing with Manual Line break (from under Special).

Now copy the table into Excel.

Edit|Replace
Replace what: $$$$$
Replace with: ctrl-j
replace all.

You may have to use Format|cells|Alignment tab|check wrap text

Don't forget to close the word document without saving (or hit undo as many
times as necessary).

==========
The second problem is the edit|Replace part causing the "formula too long"
error.

You can use a macro to do the change:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = "$$$$$"
AfterStr = " " 'or chr(10) 'for alt-enter

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

 

[Guest]

Interesting suggestion Dave but I'd like to stretch you a bit.

I was hopeing for a solution where I didn't have to do lots of cut and
paste. I have several hundred Word Documents with about 10 patient dictations
in each. Each patient dictation is divided by a hard page break. To upload
properly into the Database I need each dictation in a separate cell (plus
other separate key field items) in a column. (They are not in tables.)

If I copy/paste the 10 patients into the formula bar the page breaks are not
recognized. If I paste into the cell the CR aren't recognized the way I need.
I've tried the replace $$$$$ you've suggested but can't get Excel to
cooperate by replacing CR for the $$$$$.

1 - my campus gets a great price for MSOffice 2007 and I can get that if it
will solve my dilemma easily. If yes, no problem.
2 - A macro may solve my dilemma if I use your $$$$$ method and use a CR in
place of a hard page break. Would I just drop in this macro and assign it a
name? I presume from my limited knowledge of macros that in AfterStr = " "
'or chr(10) 'for alt enter I need to get rid of the " " and just use chr(10)?

Thanks,
philr

 

[Dave Peterson]

I wouldn't paste into the formula bar for more than a couple.

But I _think_ that this would work.

Inside MSWord:
Change the paragraph marks (and soft returns) to $$$$$.
But then change the new page to a paragraph mark.

Then copy and paste into excel
The text separated by paragraph marks should go into different cells. The text
with $$$$$ will be seen as just plain old text and go into that giant cell.

I haven't tested this on xl2007. I wouldn't imagine that something this basic
(how excel sees paragraph marks) would change in any version--maybe some one
could test and post back.

And yep. Change the afterstr to a space character (" ") or an alt-enter
(chr(10) or even vblf) if you want a new line in that cell.

And read those notes by David McRitchie. It should help you install the macro
and show you how to run it.

If you have questions, post back.

Here's hoping that someone with xl2007 handy will test and post back with their
results.

 

[Guest]

Actually I was thinking a new version of Excel might have beefed up the
Replace with option to be similar to Word. In Word there are a ton of
document options in both the find and replace with command.

philr

 

[Dave Peterson]

To avoid the "formula too long error"?

Maybe someone will test and post back.

 

[Dave Peterson]

I did a minor test in xl2007.

I added a few cells with 15000 characters
=rept("asdf ",3000)
converted to values

Then tried Edit|replace to change "asdf" to "qwer"
It worked.

I tried the same test in xl2003 and it failed with the "formula too long" error
message.

This was a minor test.

You may want to try to find someone local to see if your data reacts the same
way.

Office 2007 is pretty expensive--both in money and time spent reorienting users
to the ribbon.

 

[Guest]

Great Dave! It worked. I used the chr(10).

Is it possible within this macro to also specify the final cell height and
width? After I paste the modified text from Word into Excel and run the macro
it's vary hard to view and possibly edit the cell because the text in the
formula bar over runs the cells below. It would be nice to make the cell
about 100 units wide by 175 units tall.

Thanks,
Phil

 

[Dave Peterson]

If you wanted all the cells to have those dimensions, then just record a macro
when you make those changes. You can copy that code to the bottom of the code.