Convert Word Outline to Excel Columns
I have an outline in word that looks something like this:
- Level 1 A
- Level 2 C
- Level 3 D
- Level 2 C
- 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!
microsoft-excel microsoft-word conversion
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
add a comment |
I have an outline in word that looks something like this:
- Level 1 A
- Level 2 C
- Level 3 D
- Level 2 C
- 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!
microsoft-excel microsoft-word conversion
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
add a comment |
I have an outline in word that looks something like this:
- Level 1 A
- Level 2 C
- Level 3 D
- Level 2 C
- 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!
microsoft-excel microsoft-word conversion
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
I have an outline in word that looks something like this:
- Level 1 A
- Level 2 C
- Level 3 D
- Level 2 C
- 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!
microsoft-excel microsoft-word conversion
microsoft-excel microsoft-word conversion
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
asked Jan 16 '18 at 19:13
RussellZRussellZ
3202616
3202616
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1286162%2fconvert-word-outline-to-excel-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
add a comment |
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.
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
add a comment |
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.
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
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.
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
edited Jan 17 '18 at 8:46
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
answered Jan 17 '18 at 4:05
teylynteylyn
17.3k22539
17.3k22539
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1286162%2fconvert-word-outline-to-excel-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
