Calculated field to split File Path
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)) |
