This VBA will do what you want. Please note, my example only looked from cell
A1 to cell
A10 - you need to update that to reflect what you need (the cell range)!
In case you don't know how to do this, add the developer to the ribbon. In the controls, click insert and add a button. Add a new macro. Insert the following inside the sub routine...
Dim i As Integer
i = 1
For Each c In Worksheets("Sheet1").Range("A1:A10").Cells 'UPDATE THIS ROW ONLY WITH THE RANGE YOU WANT TO USE. This loops through all the cells in the range
Dim resultsString As String
resultsString = ""
Dim splitString() As String
splitString = Split(c, "\") ' split the value of the current cell by \
For Each v In splitString
If v <> "" Then 'only check those with a value
Dim numberOfDigits As Integer
numberOfDigits = 0
For charCount = 1 To Len(v)
If IsNumeric(Left(v, charCount)) Then
numberOfDigits = charCount ' read how many characters there are (which are numbers)
If (numberOfDigits > 0) Then
resultsString = resultsString & Left(v, numberOfDigits) & "." 'if there are numbers, then read that number of digits from the left
Dim iLength As Integer
iLength = Len(resultsString)
If (iLength > 0) Then ' if there are any values
Range("B" & i).Value = Left(resultsString, iLength - 1) 'UPDATE THIS ROW ONLY IF YOU WANT TO USE A DIFFERENT COLUMN THAN B TO SHOW RESULTS. This takes the value - 1 character (sicne the last character is a .
i = i + 1
I've added a screen of the results
c:\1_Folder\2_Folder\3_Folderexists as a field? EG,