Extract Common Text

MrDB4Excel

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
334
Office Version
  1. 2013
Platform
  1. Windows
I have a folder in which I retain downloaded teaching aids that are either xlsm or xlsx; and YouTube & website links as well as various documents related to the reason for the folder name.
Following is the folder name including the sub-folder names.
Main folder: SpecialExcelSheets
Sub-folders as follows:
AdvancedFiltering
AUTO-Fill PDF Forms
AutoSetTabOrderSaveLoadData
Calendars
Financial-ExpenseTracking
Find Date Get Row Number
Horizontal-VerticalTabs
HyperlinkFromDropDown
Miscellaneous
Misc-Websites
MultipleDropDownLists
Numbers2Words
Range-CellAddress-VLookUp-Text
RegexFormula
RGB-Colors
Security
SlideOutMenu

This list of sub-folders is likely to change, as in additional reasons for new folder names depending on some specific function etcetera.
Therefore I want to extract from the list in column B (acquired from Power Query) the sub-folders' name and have that appear in one cell, but not as I have shown in the merged cells that follow from column B.
Upon completion of this, I will have columns A & B hidden, and then any time I refresh the Power Query the changes show up on this sheet, including in addition to a new sub-folder but also a sub-folder name that was changed for whatever reason.
Forgot to mention. When I did the Power Query and edited the sheet I removed all columns showing dates and file names. I am not interested in the file names but only the folder names.
This worksheet includes a tab for each sub-folder where I have links to the various excel sheets, websites, and YouTubes associated with the specific sub-folder name.
ConsolidatedListSpecialExcel.xlsm
ABC
1IndexFile Name
21F:\Finances\CSOB2\SpecialExcelSheets\
32F:\Finances\CSOB2\SpecialExcelSheets\
43F:\Finances\CSOB2\SpecialExcelSheets\
54F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\AdvancedFiltering
65F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
76F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
87F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
98F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
109F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
1110F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
1211F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\AUTO-Fill PDF Forms
1312F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1413F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1514F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1615F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1716F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\AutoSetTabOrderSaveLoadData\
1817F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\
1918F:\Finances\CSOB2\SpecialExcelSheets\Calendars\Calendars
2019F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2120F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2221F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2322F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\Financial-ExpenseTracking\
2423F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2524F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2625F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2726F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2827F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2928F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3029F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3130F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3231F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3332F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3433F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3534F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3635F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3736F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3837F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3938F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4039F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4140F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4241F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4342F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4443F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4544F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4645F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4746F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4847F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4948F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
5049F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
FNames-Links
Cell Formulas
RangeFormula
A2:B50A2=VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)
Named Ranges
NameRefers ToCells
FolderNames!ExternalData_1=FolderNames!$A$4:$B$163A2:A50
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
MrDB4Excel said:
I have a folder in which I retain downloaded teaching aids that are either xlsm or xlsx; and YouTube & website links as well as various documents related to the reason for the folder name.
Following is the folder name including the sub-folder names.
Main folder: SpecialExcelSheets
Sub-folders as follows:
AdvancedFiltering
AUTO-Fill PDF Forms
AutoSetTabOrderSaveLoadData
Calendars
Financial-ExpenseTracking
Find Date Get Row Number
Horizontal-VerticalTabs
HyperlinkFromDropDown
Miscellaneous
Misc-Websites
MultipleDropDownLists
Numbers2Words
Range-CellAddress-VLookUp-Text
RegexFormula
RGB-Colors
Security
SlideOutMenu

This list of sub-folders is likely to change, as in additional reasons for new folder names depending on some specific function etcetera.
Therefore I want to extract from the list in column B (acquired from Power Query) the sub-folders' name and have that appear in one cell, but not as I have shown in the merged cells that follow from column B.
Upon completion of this, I will have columns A & B hidden, and then any time I refresh the Power Query the changes show up on this sheet, including in addition to a new sub-folder but also a sub-folder name that was changed for whatever reason.
Forgot to mention. When I did the Power Query and edited the sheet I removed all columns showing dates and file names. I am not interested in the file names but only the folder names.
This worksheet includes a tab for each sub-folder where I have links to the various excel sheets, websites, and YouTubes associated with the specific sub-folder name.
ConsolidatedListSpecialExcel.xlsm
ABC
1IndexFile Name
21F:\Finances\CSOB2\SpecialExcelSheets\
32F:\Finances\CSOB2\SpecialExcelSheets\
43F:\Finances\CSOB2\SpecialExcelSheets\
54F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\AdvancedFiltering
65F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
76F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
87F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
98F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
109F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
1110F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
1211F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\AUTO-Fill PDF Forms
1312F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1413F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1514F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1615F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1716F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\AutoSetTabOrderSaveLoadData\
1817F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\
1918F:\Finances\CSOB2\SpecialExcelSheets\Calendars\Calendars
2019F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2120F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2221F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2322F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\Financial-ExpenseTracking\
2423F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2524F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2625F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2726F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2827F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2928F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3029F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3130F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3231F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3332F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3433F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3534F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3635F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3736F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3837F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3938F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4039F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4140F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4241F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4342F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4443F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4544F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4645F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4746F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4847F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4948F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
5049F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
FNames-Links
Cell Formulas
RangeFormula
A2:B50A2=VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)
Named Ranges
NameRefers ToCells
FolderNames!ExternalData_1=FolderNames!$A$4:$B$163A2:A50
Click to expand...
Again, assuming it is okay to post an update to this original as the time for editing the original has long past.
Update, 13 Aug 2020:

Upon further research, to a variety of possible solutions, I came upon one that at first glance seems to provide a viable solution, but in the end, it does not do what I failed to mention in my first post. What is that you might ask?
Simply I need the formulas you will see in the attached per the XL2BB capture to cover a range of similar sub-folder names so that when and if my list of subfolders increases thus pushing some of the lists down then the formulas remain with the range of similar sub-folder names.
Two Examples:
1) Rows 123, 124 & 125 indicate a subfolder name of MultipleDropDownLists. The formula to extract the text is =RIGHT(B124, LEN(B124)-37).
The cell is per row 124 which is in the middle of the range. Let's assume for the purpose of this post that I add 3 files to the Miscellaneous sub-folder. When a refresh is done on the Power Query then the 3 rows 123 – 125 for the MultipleDropDownLists subfolder suddenly change to 126 – 128 for the MultipleDropDownLists sub-folder. This will cause the formula =RIGHT(B124, LEN(B124)-37) to no longer return a result of MultipleDropDownLists but instead will return Miscellaneous.
2) Rows 141, 142, 143, 144 & 145 indicate a subfolder name of RegexFormula. The formula to extract the text is =RIGHT(B143, LEN(B143)-37).
The cell is per row 143 which is in the middle of the range. Let's assume for the purpose of this post that I add 8 files to the Range-CellAddress-VLookUp-Text sub-folder. When a refresh is done on the Power Query then the 5 rows 141 – 145 for the RegexFormula subfolder suddenly change to 149 – 153 for the RegexFormula sub-folder. This will cause the formula =RIGHT(B124, LEN(B124)-37) to no longer return a result of RegexFormula but instead will return Range-CellAddress-VLookUp-Text.
Now it is clearly evident why these formulas must somehow encompass a dynamic range of rows for each sub-folder that update when changes are made in the main folder SpecialExcelSheets.
You may leave school, but it never leaves you. Never stop learning!!!
Any help would be much appreciated.

ConsolidatedListSpecialExcel.xlsm
ABCD
1IndexFolder PathFolder Name
21F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\AdvancedFiltering
32F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\AUTO-Fill PDF Forms
43F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\AutoSetTabOrderSaveLoadData
54F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Calendars\Calendars
65F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Financial-ExpenseTracking\Financial-ExpenseTracking
76F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Find Date Get Row Number\Find Date Get Row Number
87F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\HyperlinkFromDropDown\HyperlinkFromDropDown
98F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Misc-Websites\Misc-Websites
109F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Miscellaneous\Miscellaneous
1110F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\MultipleDropDownLists\MultipleDropDownLists
1211F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\Numbers2Words\Numbers2Words
1312F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\Range-CellAddress-VLookUp-Text\Range-CellAddress-VLookUp-Text
1413F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\RegexFormula\RegexFormula
1514F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\RGB-Colors\RGB-Colors
1615F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\RGB-Colors\These2SitesTheBest\RGB-Colors\These2SitesTheBest
1716F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\Security\Security
1817F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\SlideOutMenu\SlideOutMenu
FNames-Links
Cell Formulas
RangeFormula
A2:B18A2=VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)
C2C2=RIGHT(B8, LEN(B8)-37)
D2:D18D2=LEFT(C2, LEN(C2)-1)
C3C3=RIGHT(B14, LEN(B14)-37)
C4C4=RIGHT(B18, LEN(B18)-37)
C5C5=RIGHT(B21, LEN(B21)-37)
C6C6=RIGHT(B29, LEN(B29)-37)
C7C7=RIGHT(B43, LEN(B43)-37)
C8C8=RIGHT(B65, LEN(B65)-37)
C9C9=RIGHT(B80, LEN(B80)-37)
C10C10=RIGHT(B103, LEN(B103)-37)
C11C11=RIGHT(B124, LEN(B124)-37)
C12C12=RIGHT(B127, LEN(B127)-37)
C13C13=RIGHT(B135, LEN(B135)-37)
C14C14=RIGHT(B143, LEN(B143)-37)
C15C15=RIGHT(B150, LEN(B150)-37)
C16C16=RIGHT(B155, LEN(B155)-37)
C17C17=RIGHT(B157, LEN(B157)-37)
C18C18=RIGHT(B159, LEN(B159)-37)
Named Ranges
NameRefers ToCells
FolderNames!ExternalData_1=FolderNames!$A$4:$B$163A2:A18


ConsolidatedListSpecialExcel.xlsm
AB
121120F:\Finances\CSOB2\SpecialExcelSheets\Miscellaneous\
122121F:\Finances\CSOB2\SpecialExcelSheets\Miscellaneous\
123122F:\Finances\CSOB2\SpecialExcelSheets\MultipleDropDownLists\
124123F:\Finances\CSOB2\SpecialExcelSheets\MultipleDropDownLists\
125124F:\Finances\CSOB2\SpecialExcelSheets\MultipleDropDownLists\
126125F:\Finances\CSOB2\SpecialExcelSheets\Numbers2Words\
127126F:\Finances\CSOB2\SpecialExcelSheets\Numbers2Words\
128127F:\Finances\CSOB2\SpecialExcelSheets\Numbers2Words\
129128F:\Finances\CSOB2\SpecialExcelSheets\Range-CellAddress-VLookUp-Text\
130129F:\Finances\CSOB2\SpecialExcelSheets\Range-CellAddress-VLookUp-Text\
FNames-Links
Cell Formulas
RangeFormula
A121:B130A121=VLOOKUP(FolderNames!A124,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)
Named Ranges
NameRefers ToCells
FolderNames!ExternalData_1=FolderNames!$A$4:$B$163A121:A130



ConsolidatedListSpecialExcel.xlsm
AB
139138F:\Finances\CSOB2\SpecialExcelSheets\Range-CellAddress-VLookUp-Text\
140139F:\Finances\CSOB2\SpecialExcelSheets\Range-CellAddress-VLookUp-Text\
141140F:\Finances\CSOB2\SpecialExcelSheets\RegexFormula\
142141F:\Finances\CSOB2\SpecialExcelSheets\RegexFormula\
143142F:\Finances\CSOB2\SpecialExcelSheets\RegexFormula\
144143F:\Finances\CSOB2\SpecialExcelSheets\RegexFormula\
145144F:\Finances\CSOB2\SpecialExcelSheets\RegexFormula\
146145F:\Finances\CSOB2\SpecialExcelSheets\RGB-Colors\
FNames-Links
Cell Formulas
RangeFormula
A139:B146A139=VLOOKUP(FolderNames!A142,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)
Named Ranges
NameRefers ToCells
FolderNames!ExternalData_1=FolderNames!$A$4:$B$163A139:A146
 
Upvote 0
See Further Use of Discovered Range where I posted this morning the following which applies equally to this post thus this post can also be closed.
This post can be closed. Receiving no answers I decided to create specific queries based on single sub-folders rather than one query on one main folder that included all its sub-folders thus eliminating the problem.
 
Upvote 0
try this
www.mrexcel.com

Power Query How to list folder names only NOT files

Hello, I am fairly new to Power Query and have searched but can't find a solution - every thing I find is related to listing files, not folders. Is it possible for power query to just list folder names from a path? I can list all files with the path and extract the folder name but removing the...
www.mrexcel.com www.mrexcel.com
you'll need to create Excel Table named FPath then enter there full path to the main folder (where all sub-folders are)
 
Last edited:
Upvote 0
I forgot to add an example so here is

Column1Folder Path
D:\testD:\test
D:\test\APIrefresh
D:\test\AlertMtl
D:\test\BaseFolder
D:\test\BaseFolder\Folder1
D:\test\BaseFolder\Folder2
D:\test\BaseFolder\Folder2\Folder2.1
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.1
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.2
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.3
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.3\Folder2.1.3.1
D:\test\BaseFolder\Folder2\Folder2.2
D:\test\Corried
D:\test\Corried\countries
D:\test\Electricity usage
D:\test\JohnGil
D:\test\JohnGil\csv

then you can extract folders after last delimiter "\"
 
Last edited:
Upvote 0
bad timing :eek:
Column1Folder PathText After Delimiter
D:\testD:\testtest
D:\test\APIrefreshAPIrefresh
D:\test\AlertMtlAlertMtl
D:\test\BaseFolderBaseFolder
D:\test\BaseFolder\Folder1Folder1
D:\test\BaseFolder\Folder2Folder2
D:\test\BaseFolder\Folder2\Folder2.1Folder2.1
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.1Folder2.1.1
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.2Folder2.1.2
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.3Folder2.1.3
D:\test\BaseFolder\Folder2\Folder2.1\Folder2.1.3\Folder2.1.3.1Folder2.1.3.1
D:\test\BaseFolder\Folder2\Folder2.2Folder2.2
D:\test\CorriedCorried
D:\test\Corried\countriescountries
D:\test\Electricity usageElectricity usage
 
Upvote 0
Thanks Sandy666,
The extraction of the last delimiter seems a bit of a difficult task. Attached XL2BB will show you what I ended up doing before your reply.
Earlier today I posted a reply to my own post as follows in part "... I decided to create specific queries based on single sub-folders rather than one query on one main folder that included all its sub-folders thus eliminating the problem."
So this is what I did and next time I create a workbook that is similar to my first attempts of the CSOB2 scenario I will give your method a try and in fact, I will run a test so I have your system contained in a file I can look to for future reference.
ConsolidatedListSpecialExcel.xlsm
ABCDEF
1F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\1zTOC
2AdvancedFiltering\1z<<< Jump To This Sheet's Link In The Summary Sheet
3AdvancedFiltering1<<< This Sheet's Name
4IndexFile NameContaining FolderConcatenated File NameHyperlinksDate Modified
51Advanced Filter TRICK in Excel - YouTube.urlF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Advanced Filter TRICK in Excel - YouTube.urlF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Advanced Filter TRICK in Excel - YouTube.url07/30/2020 22:30
62Advanced_Filter_Trick_Example.xlsxF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Advanced_Filter_Trick_Example.xlsxF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Advanced_Filter_Trick_Example.xlsx07/30/2020 22:32
73Excel Advanced Filter Trick - Xelplus - Leila Gharani.urlF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Excel Advanced Filter Trick - Xelplus - Leila Gharani.urlF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Excel Advanced Filter Trick - Xelplus - Leila Gharani.url07/30/2020 22:29
84ExcelzAdvancedFilterLittleKnownTrick.docxF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\ExcelzAdvancedFilterLittleKnownTrick.docxF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\ExcelzAdvancedFilterLittleKnownTrick.docx08/11/2020 09:56
95Filter Excel Table Data By Entering Text In The Header - YouTube.urlF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Filter Excel Table Data By Entering Text In The Header - YouTube.urlF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Filter Excel Table Data By Entering Text In The Header - YouTube.url08/04/2020 13:40
106Table_Header_Filtering.xlsmF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Table_Header_Filtering.xlsmF:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\Table_Header_Filtering.xlsm08/04/2020 13:45
11#N/A#N/A#N/A#N/A#N/A#N/A
12#N/A#N/A#N/A#N/A#N/A#N/A
1
Cell Formulas
RangeFormula
B1B1=RIGHT(C5,LEN(C5)-0)
C1C1=LEFT(C3,1)&"z"
B2B2=MID(B1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),4))+1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),5)) - FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),2))-1)
C2C2=C1
D2D2=HYPERLINK("#"&CELL("address",INDEX(Summary!$E$2:$E$33,MATCH($C$2,Summary!$E$2:$E$33,0))),"<<< Jump To This Sheet's Link In The Summary Sheet")
B3B3=LEFT(B2,LEN(B2)-1)
C3C3=MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)
A5:A12A5=VLOOKUP('1z'!$A5,'1z'!$A:$A,COLUMN('1z'!$A:$A)-COLUMN('1z'!$A:$A)+1,0)
B5:B12B5=VLOOKUP('1z'!$B5,'1z'!$B:$B,COLUMN('1z'!$B:$B)-COLUMN('1z'!$B:$B)+1,0)
C5:C12C5=VLOOKUP('1z'!$C5,'1z'!$C:$C,COLUMN('1z'!$C:$C)-COLUMN('1z'!$C:$C)+1,0)
D5:D12D5=CONCATENATE(C5,B5)
E5:E12E5=HYPERLINK(D5)
F5:F12F5=VLOOKUP('1z'!$D5,'1z'!$D:$D,COLUMN('1z'!$D:$D)-COLUMN('1z'!$D:$D)+1,0)
Named Ranges
NameRefers ToCells
'1z'!ExternalData_1='1z'!$A$4:$D$10A5:A12
 
Upvote 0
to extract folders after last delimiter enough to add this line after last line in the code
Power Query: 
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Sorted Rows", "Text After Delimiter", each Text.AfterDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd}), type text)
links in PQ are a bit complicated because PQ doesn't support links

but I see you did whole path to the files and it will do what you want
then you can split whole path to :
- sub-folder
- filenames
enough to use option From Folder then split (easier way)
 
Upvote 0
In the code on any given power query edit session?
Or is there another specific location this code should be applied to?
 
Upvote 0
MrDB4Excel said:
In the code on any given power query edit session?
Or is there another specific location this code should be applied to?
Click to expand...
If you asking about after last delimiter you will need add line from post#8 to the code from post#4

but I really suggest option From Folder, more flexible :cool: (y)

eg.
D:\test\test\TestFolder.zip
D:\test\adamjones\adamjones\metrics.xlsx
D:\test\adamjones\Production\Production\production01062019.xlsx
D:\test\adamjones\Production\Production\production02062019.xlsx
D:\test\adamjones\Production\Production\production03062019.xlsx
D:\test\AlertMtl\AlertMtl\0000000001B01.sym
D:\test\AlertMtl\AlertMtl\0003042.sym
D:\test\AlertMtl\AlertMtl\MC8UJ01210B01.sym
D:\test\AlertMtl\AlertMtl\MC8UJ01213B01.sym
D:\test\AlertMtl\AlertMtl\MM8UB00080B01.sym
D:\test\AlertMtl\AlertMtl\MM8UB00081B01.sym
D:\test\anastasia\anastasia\17.1-A.txt
D:\test\anastasia\anastasia\72.5-A.txt
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Question
Further Use of Discovered Range
Replies
2
Views
282
MrDB4Excel
MrDB4Excel
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
K
  • Question
Need to create/source a macro/vba/program that auto populates info based on matching word criteria for community support agency
Replies
3
Views
449
Micron
M
MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
389
MrDB4Excel
MrDB4Excel
Arie Bos
  • Solved
extracting number from foldername
Replies
6
Views
351
Arie Bos
Arie Bos

Forum statistics

Threads
1,214,651
Messages
6,120,744
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top