Requirement:

Created a calculated field in a SharePoint list/library to split a filepath and store the first 3 folder levels in 3 separate metadata fields.

For example:

C:\Users\Admin\Desktop\test.txt

 Level 1 Level 2  Level 3
Users  Admin  Desktop

Solution

Assuming MigrationSourceURL is the column in SharePoint that stores the file path:

Level 1 formula

=IF(MigrationSourceURL="","N/A",LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))

Level 2 formula

=IF(MigrationSourceURL="","N/A",LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4))-1))

Level 3 formula

=IF(MigrationSourceURL="","N/A",LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4))-1))-1),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-LEN(LEFT(RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3),SEARCH("\",RIGHT(MigrationSourceURL,LEN(MigrationSourceURL)-3))-1))-4))-1))-1))-1))