How to populate "Document Name" in the Index File

How to populate "Document Name" in the Index File

 

When preparing your index file for the bulk upload utility it can be difficult to populate the "Document Name" column, especially if there are a different number of subfolders within each file path. In this article, you will find an easy excel trick on how to pull the document name from a list of file paths that have a different number of subfolders.

For this article, I'll use the example file paths below and show you how to populate a column with just the document names from each unique file path.

Row 1 - Drive/Top Folder/Subfolder 1/Doc Name A
Row 2 - Drive/Top Folder/Subfolder 1/Subfolder 2/Doc Name B
Row 3 - Drive/Top Folder/Subfolder 1/Subfolder2/Subfolder 3/Doc Name C

Step 1:

In Excel, copy and paste the entire column of file paths into a new sheet. Select the column of file paths and select "Text To Columns" under the Data tab. Select "Delimited" and click next, the next window only select "Other" under Delimiters and type in the field next to it "/" without the quotations and press finish.

Text_To_Columns.PNG

 

Step 2:

Insert a new column in column A. In the new column, copy the following excel formula into Cell A2.

=VLOOKUP(B2,B2:XFD2,COUNTA(B2:XFD2),FALSE)

Copy that formula down to the end of your data table, and you should see just the document name populated.

Formula_Example.PNG

Copy and paste column A into the Document Field in your index file, and you are done!

 

If you would like to see the example from this article please download the attached spreadsheet.

 

If you have further questions, please submit a ticket or contact support@thoughttrace.com

 

 

Was this article helpful?
0 out of 0 found this helpful