import indented outline from word into excel

  • Thread starter Thread starter ionnet
  • Start date Start date
I

ionnet

I've searched for answer to this but can't find a working solution.
help!!!!!

I've created an outline in msword that looks like this:

1. text a
1.1. text a1
2. text b
2.1. text b1
2.1.1. text b1a
2.1.1.1. text b1a1
2.1.1.2. text b1a2
3. text c



I've seen excel spreadsheet created that looks like this:

Column A B C D E
1. text a
1.1 text a1
2. text b
2.1 text b1
2.1.1 text b1a
2.1.1.1 text b1a1
2.1.1.2 text b1a2
3. text c

can someone on earth tell me how this is done?

it can't be straight import cuz I tried every which way.
 
Since they look identical here, can you describe what is
wrong and what you wanted. Also what version of Word
and what version of Excel. Is the word "text" supposed
to line up vertically or are they supposed to be indented
more as more levels are thrown into the outline number.
 
David,

thanks for the reply, I didn't know the spacing i put in
got lost when posting my message.

what I'm trying to describe is in Word I got an outline indentation
going.
when I do a copy and paste to Excel, I got the numbers
in the first column A, and text in column B.

what I want to happen in Excel is to have column A have
all the indent numbers and then column B have level 1 header,
column C have level 1.1 header, and so on.

can you help me?
 
I'm using Word 2000 and Excel2000.

thanks!


David,

thanks for the reply, I didn't know the spacing i put in
got lost when posting my message.

what I'm trying to describe is in Word I got an outline indentation
going.
when I do a copy and paste to Excel, I got the numbers
in the first column A, and text in column B.

what I want to happen in Excel is to have column A have
all the indent numbers and then column B have level 1 header,
column C have level 1.1 header, and so on.

can you help me?
Click to expand...
 
I created a test outline in MSWord.

I created a new sheet in Excel.
In that new sheet, I formatted the cells as text
(ctrl-A, Edit|format|number tab|Text)

Then back to word to copy the outline.

then back to excel.
I rightclicked on A1 and selected paste special. I selected Text from that
funny paste|special dialog. (not the usual one you see when you copy inside of
excel.)

I got this:

1. asdf
2. qwer
2.1. esadf
2.1.1. fasdf
2.1.2. qwerq
2.1.3. asdf
2.2. asdf
3. asdf
4. asdf
4.1. asdfadsf
4.2. asdfasdf
4.3. asdfasdf
4.3.1. asdf
4.3.2. asdf
4.3.3. asdf
4.3.4. asdf
5. asdf

in two columns.

Now the number of dots in column A indicate what column the text should be in
(well, plus 1).

1 dot = column B.
3 dots = column D
....

So I could run this code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim dotCtr As Long

Set wks = ActiveSheet

With wks
Set myRng = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
For Each myCell In myRng.Cells
With myCell
dotCtr = Len(.Value) _
- Len(Application.Substitute(.Value, ".", ""))
If dotCtr = 0 Then
dotCtr = 1 'just in case no dots!
End If
If dotCtr > 1 Then
.Offset(0, dotCtr + 1).Value = .Offset(0, 1).Value
.Offset(0, 1).ClearContents
End If
End With
Next myCell
End With

End Sub

and get this output:

1. asdf
2. qwer
2.1. esadf
2.1.1. fasdf
2.1.2. qwerq
2.1.3. asdf
2.2. asdf
3. asdf
4. asdf
4.1. asdfadsf
4.2. asdfasdf
4.3. asdfasdf
4.3.1. asdf
4.3.2. asdf
4.3.3. asdf
4.3.4. asdf
5. asdf



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

Inside the code, change this line:

..Offset(0, dotCtr + 1).Value = .Offset(0, 1).Value
to
..Offset(0, dotCtr).Value = .Offset(0, 1).Value

Sorry!
 
hey bro, you are THE MAN!!!!!

man it worked like a charm!

thanks bro. you are awesome!!!!!!!
 
Back
Top