Split folder paths in Excel to return the final folder
I have a single column in Excel which has a file and folder path. e.g. C:1_Folder2_Folder3_Foldermy_file.txt
I would like to extract the name of the final folder and place this in a new column. In this example, 3_Folder.
Can this be achieved using a formula rather than VBA?
Edit: the number of nested folders can vary.
microsoft-excel string string-manipulation
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
add a comment |
I have a single column in Excel which has a file and folder path. e.g. C:1_Folder2_Folder3_Foldermy_file.txt
I would like to extract the name of the final folder and place this in a new column. In this example, 3_Folder.
Can this be achieved using a formula rather than VBA?
Edit: the number of nested folders can vary.
microsoft-excel string string-manipulation
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
add a comment |
I have a single column in Excel which has a file and folder path. e.g. C:1_Folder2_Folder3_Foldermy_file.txt
I would like to extract the name of the final folder and place this in a new column. In this example, 3_Folder.
Can this be achieved using a formula rather than VBA?
Edit: the number of nested folders can vary.
microsoft-excel string string-manipulation
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
I have a single column in Excel which has a file and folder path. e.g. C:1_Folder2_Folder3_Foldermy_file.txt
I would like to extract the name of the final folder and place this in a new column. In this example, 3_Folder.
Can this be achieved using a formula rather than VBA?
Edit: the number of nested folders can vary.
microsoft-excel string string-manipulation
microsoft-excel string string-manipulation
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
edited Sep 8 '15 at 12:39
BobJim
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
asked Sep 8 '15 at 10:36
BobJimBobJim
4853821
4853821
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
You can use the FIND and MID text functions. This will work for variable number of folders
Path text C:1_Folder2_Folder3_Foldermy_file.txt
Find position of next to last slash (B1): FIND("|",SUBSTITUTE(A1,"","|",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))-1))
Find position of last slash (C1): FIND("",A1,B1+1)
Get the characters between next to last and last slash: MID(A1,B1+1,(C1-B1)-1)
answered Sep 8 '15 at 11:03
ToddTodd
21126
That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work?
– BobJim
Sep 8 '15 at 12:14
I just wrote this based on your example. I have amended my answer for variable number of folders.
– Todd
Sep 8 '15 at 14:05
Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
– BobJim
Sep 8 '15 at 14:14
1
Typos, fixed it.
– Todd
Sep 8 '15 at 14:21
add a comment |
I use regex addin for tasks like this, with regular expression:
=RegExReplace(A1,".*\([^\]*)\[^\]*","$1") - this extracts the substring before the last (practically the last folder as you need)
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
That's pretty slick. Would be nice if Excel supported regular expressions natively.
– Todd
Sep 8 '15 at 14:54
add a comment |
This answer is based on the accepted answer to a very similar question.
If you want it in one formula, you can use:
=RIGHT(A6,LEN(A6)+1-FIND("@",SUBSTITUTE(""&A6,"","@",(LEN(A6)-LEN(SUBSTITUTE(""&A6,"",""))+1))))
... which basically calculates the number of backslashes by removing them all and comparing lengths. It uses that number to substitutes the last occurrence with a "@" and then turns back around and finds the position of the "@" which it feeds into the RIGHT formula to grab the substring trailing the last backslash. An extra backslash and a "+1" are added in to handle the case of zero backslashes in the source. If there are no backslashes, the original string is returned.
If your data already contains "@" then you will need to pick a different substitution character.
edited May 23 '17 at 12:41
Community♦
1
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
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%2f969898%2fsplit-folder-paths-in-excel-to-return-the-final-folder%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use the FIND and MID text functions. This will work for variable number of folders
Path text C:1_Folder2_Folder3_Foldermy_file.txt
Find position of next to last slash (B1): FIND("|",SUBSTITUTE(A1,"","|",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))-1))
Find position of last slash (C1): FIND("",A1,B1+1)
Get the characters between next to last and last slash: MID(A1,B1+1,(C1-B1)-1)
answered Sep 8 '15 at 11:03
ToddTodd
21126
That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work?
– BobJim
Sep 8 '15 at 12:14
I just wrote this based on your example. I have amended my answer for variable number of folders.
– Todd
Sep 8 '15 at 14:05
Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
– BobJim
Sep 8 '15 at 14:14
1
Typos, fixed it.
– Todd
Sep 8 '15 at 14:21
add a comment |
You can use the FIND and MID text functions. This will work for variable number of folders
Path text C:1_Folder2_Folder3_Foldermy_file.txt
Find position of next to last slash (B1): FIND("|",SUBSTITUTE(A1,"","|",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))-1))
Find position of last slash (C1): FIND("",A1,B1+1)
Get the characters between next to last and last slash: MID(A1,B1+1,(C1-B1)-1)
answered Sep 8 '15 at 11:03
ToddTodd
21126
That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work?
– BobJim
Sep 8 '15 at 12:14
I just wrote this based on your example. I have amended my answer for variable number of folders.
– Todd
Sep 8 '15 at 14:05
Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
– BobJim
Sep 8 '15 at 14:14
1
Typos, fixed it.
– Todd
Sep 8 '15 at 14:21
add a comment |
You can use the FIND and MID text functions. This will work for variable number of folders
Path text C:1_Folder2_Folder3_Foldermy_file.txt
Find position of next to last slash (B1): FIND("|",SUBSTITUTE(A1,"","|",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))-1))
Find position of last slash (C1): FIND("",A1,B1+1)
Get the characters between next to last and last slash: MID(A1,B1+1,(C1-B1)-1)
answered Sep 8 '15 at 11:03
ToddTodd
21126
You can use the FIND and MID text functions. This will work for variable number of folders
Path text C:1_Folder2_Folder3_Foldermy_file.txt
Find position of next to last slash (B1): FIND("|",SUBSTITUTE(A1,"","|",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))-1))
Find position of last slash (C1): FIND("",A1,B1+1)
Get the characters between next to last and last slash: MID(A1,B1+1,(C1-B1)-1)
answered Sep 8 '15 at 11:03
ToddTodd
21126
edited Sep 8 '15 at 14:20
answered Sep 8 '15 at 11:03
ToddTodd
21126
answered Sep 8 '15 at 11:03
ToddTodd
21126
answered Sep 8 '15 at 11:03
ToddTodd
21126
21126
That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work?
– BobJim
Sep 8 '15 at 12:14
I just wrote this based on your example. I have amended my answer for variable number of folders.
– Todd
Sep 8 '15 at 14:05
Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
– BobJim
Sep 8 '15 at 14:14
1
Typos, fixed it.
– Todd
Sep 8 '15 at 14:21
add a comment |
That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work?
– BobJim
Sep 8 '15 at 12:14
I just wrote this based on your example. I have amended my answer for variable number of folders.
– Todd
Sep 8 '15 at 14:05
Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
– BobJim
Sep 8 '15 at 14:14
1
Typos, fixed it.
– Todd
Sep 8 '15 at 14:21
That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work?
– BobJim
Sep 8 '15 at 12:14
That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work?
– BobJim
Sep 8 '15 at 12:14
I just wrote this based on your example. I have amended my answer for variable number of folders.
– Todd
Sep 8 '15 at 14:05
I just wrote this based on your example. I have amended my answer for variable number of folders.
– Todd
Sep 8 '15 at 14:05
Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
– BobJim
Sep 8 '15 at 14:14
Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
– BobJim
Sep 8 '15 at 14:14
1
1
Typos, fixed it.
– Todd
Sep 8 '15 at 14:21
Typos, fixed it.
– Todd
Sep 8 '15 at 14:21
add a comment |
I use regex addin for tasks like this, with regular expression:
=RegExReplace(A1,".*\([^\]*)\[^\]*","$1") - this extracts the substring before the last (practically the last folder as you need)
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
That's pretty slick. Would be nice if Excel supported regular expressions natively.
– Todd
Sep 8 '15 at 14:54
add a comment |
I use regex addin for tasks like this, with regular expression:
=RegExReplace(A1,".*\([^\]*)\[^\]*","$1") - this extracts the substring before the last (practically the last folder as you need)
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
That's pretty slick. Would be nice if Excel supported regular expressions natively.
– Todd
Sep 8 '15 at 14:54
add a comment |
I use regex addin for tasks like this, with regular expression:
=RegExReplace(A1,".*\([^\]*)\[^\]*","$1") - this extracts the substring before the last (practically the last folder as you need)
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
I use regex addin for tasks like this, with regular expression:
=RegExReplace(A1,".*\([^\]*)\[^\]*","$1") - this extracts the substring before the last (practically the last folder as you need)
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
answered Sep 8 '15 at 13:52
Máté JuhászMáté Juhász
14.4k63352
14.4k63352
That's pretty slick. Would be nice if Excel supported regular expressions natively.
– Todd
Sep 8 '15 at 14:54
add a comment |
That's pretty slick. Would be nice if Excel supported regular expressions natively.
– Todd
Sep 8 '15 at 14:54
That's pretty slick. Would be nice if Excel supported regular expressions natively.
– Todd
Sep 8 '15 at 14:54
That's pretty slick. Would be nice if Excel supported regular expressions natively.
– Todd
Sep 8 '15 at 14:54
add a comment |
This answer is based on the accepted answer to a very similar question.
If you want it in one formula, you can use:
=RIGHT(A6,LEN(A6)+1-FIND("@",SUBSTITUTE(""&A6,"","@",(LEN(A6)-LEN(SUBSTITUTE(""&A6,"",""))+1))))
... which basically calculates the number of backslashes by removing them all and comparing lengths. It uses that number to substitutes the last occurrence with a "@" and then turns back around and finds the position of the "@" which it feeds into the RIGHT formula to grab the substring trailing the last backslash. An extra backslash and a "+1" are added in to handle the case of zero backslashes in the source. If there are no backslashes, the original string is returned.
If your data already contains "@" then you will need to pick a different substitution character.
edited May 23 '17 at 12:41
Community♦
1
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
add a comment |
This answer is based on the accepted answer to a very similar question.
If you want it in one formula, you can use:
=RIGHT(A6,LEN(A6)+1-FIND("@",SUBSTITUTE(""&A6,"","@",(LEN(A6)-LEN(SUBSTITUTE(""&A6,"",""))+1))))
... which basically calculates the number of backslashes by removing them all and comparing lengths. It uses that number to substitutes the last occurrence with a "@" and then turns back around and finds the position of the "@" which it feeds into the RIGHT formula to grab the substring trailing the last backslash. An extra backslash and a "+1" are added in to handle the case of zero backslashes in the source. If there are no backslashes, the original string is returned.
If your data already contains "@" then you will need to pick a different substitution character.
edited May 23 '17 at 12:41
Community♦
1
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
add a comment |
This answer is based on the accepted answer to a very similar question.
If you want it in one formula, you can use:
=RIGHT(A6,LEN(A6)+1-FIND("@",SUBSTITUTE(""&A6,"","@",(LEN(A6)-LEN(SUBSTITUTE(""&A6,"",""))+1))))
... which basically calculates the number of backslashes by removing them all and comparing lengths. It uses that number to substitutes the last occurrence with a "@" and then turns back around and finds the position of the "@" which it feeds into the RIGHT formula to grab the substring trailing the last backslash. An extra backslash and a "+1" are added in to handle the case of zero backslashes in the source. If there are no backslashes, the original string is returned.
If your data already contains "@" then you will need to pick a different substitution character.
edited May 23 '17 at 12:41
Community♦
1
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
This answer is based on the accepted answer to a very similar question.
If you want it in one formula, you can use:
=RIGHT(A6,LEN(A6)+1-FIND("@",SUBSTITUTE(""&A6,"","@",(LEN(A6)-LEN(SUBSTITUTE(""&A6,"",""))+1))))
... which basically calculates the number of backslashes by removing them all and comparing lengths. It uses that number to substitutes the last occurrence with a "@" and then turns back around and finds the position of the "@" which it feeds into the RIGHT formula to grab the substring trailing the last backslash. An extra backslash and a "+1" are added in to handle the case of zero backslashes in the source. If there are no backslashes, the original string is returned.
If your data already contains "@" then you will need to pick a different substitution character.
edited May 23 '17 at 12:41
Community♦
1
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
1
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
answered Sep 8 '15 at 13:36
GuitarPickerGuitarPicker
1,012515
1,012515
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%2f969898%2fsplit-folder-paths-in-excel-to-return-the-final-folder%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
