Convert Word Outline to Excel Columns

Question

I have an outline in word that looks something like this:

• Level 1 A
  • Level 2 C
    • Level 3 D
• Level 1 B

I want to convert it to columns based on the outline level/indentation:

Col 1        Col 2        Col 3
Level 1 A  
             Level 2 C
                          Level 3 D
Level 1 B

Eventually, I want to get it into database format:

ID           Parent
Level 1 A  
Level 2 C    Level 1 A
Level 3 D    Level 2 C
Level 1 B

I'm having the most difficulty with the first part, but if you have any tips on this part, that would be great too!

Answer 1

In Word, use the multi level numbering for the headings (just change the level 1 heading style to apply multi-level numbers and all other heading styles will adapt the numbering). Now all headings will have numbers like 2, 2.1, 2.2.2, followed by the heading text.

Next, create a table of contents, which will have only the headings. Use the first style in the list of TOC styles in the dialog. By default, a TOC contains only headings up to four levels, so you may need to change the heading styles if you want to include more levels in the TOC.

Copy the TOC and paste it into Excel, using Paste Special > Unicode text. The paste result will be three columns, i.e. the number of the heading, the heading text and the page number. Select the number column and set its format to "Text". If you don't do that, the following will not work.

Use formulas to calculate the level according to the dots in the numbers column. Then use formulas to pull the text into different columns accordingly, as shown below.

In the following screenshot, the level is calculated with the formula (in E2, copied down)

=LEN(A2)-LEN(SUBSTITUTE(A2,".",""))+1

The formula in F2 is

=IF($E2=F$1,$B2,"")

copied across and down.