Extracting Characters from Folder Path in Excel

Question

I have a huge list of folder paths as text in an Excel worksheet from which I would like to extract the number after each backslash. These numbers then need compiling into a shortened reference. For example:

Original link (let's say in cell A1):

c:\1_Folder\2_Folder\3_Folder

Shortened reference (desired outcome):

1.2.3

Correct me if I'm wrong but I don't think this is possible using a formula. If it requires the use of VBA, this is far beyond my skills currently.

Can you suggest a solution?

Answer 1

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)
                    End If
                Next

                If (numberOfDigits > 0) Then
                    resultsString = resultsString & Left(v, numberOfDigits) & "." 'if there are numbers, then read that number of digits from the left
                End If
            End If
        Next

        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 .
        End If

        i = i + 1
    Next  

I've added a screen of the results

Answer 2

1. Go to data - text to columns
2. Delimit based on the \
3. Then you can use a formula like =LEFT(A2,1)&"."&LEFT(B2,1)&"."&LEFT(C2,1)

---

VBA - assuming your data is in column A

Sub Search_DelimitedSubString_In_String()
Application.ScreenUpdating = False
Dim strFolder As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
j = 1
'Loop through cells
For Each c In Range("A:A")
    'Select non-blanks
    If c.Value <> "" Then
        On Error Resume Next
        For i = 1 To Len(c.Value)
            If Mid(c.Value, i, 1) = "\" Then
                If IsNumeric(Mid(c.Value, i + 1, 1)) Then
                strFolder = strFolder & Mid(c.Value, i + 1, 1) & "."
                End If
            End If

        Next

    Cells(j, 2) = strFolder
    j = j + 1
    End If
 strFolder = ""
 Next c

 For Each c In Range("B:B")
 If c.Value <> "" Then
    k = Len(c.Value)
    c.Value = Left(c.Value, k - 1)
 End If
 Next

Application.ScreenUpdating = True
End Sub

Answer 3

Assuming all your data is in column A, then column B would look like:

Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")
RE.Global = True
RE.Pattern = "\\(\d+)"

For Each c In UsedRange.Columns(1).Cells
    Set matches = RE.Execute(c)
    ReDim all(matches.Count - 1)
    For i = 0 To matches.Count - 1
        all(i) = matches.Item(i).Submatches(0)
    Next
    c.Offset(0, 1) = Join(all(), ".")
Next

I used VBA Regex, two loops and an array Join

VBA RegEx doesn't supported lookbehinds or else you could use (?<=\\)\d+.

Answer 4

is it important to use vba? if you are using version 2013 of Excel its so much easier to use "Flash Fill" feature to do that...

Flash Fill is a feature to fill the cells by the pattern of the cells next to them:

just fill the A1, A2, A3,... with the strings you want to extract chars from, fill the B1 as the way you want answer:

in B2 press CTRL + E, excel will fill other cells as you want:

Answer 5

If "Folder" is really "Folder" (and not a common name for special folder names), then this formula will be the fastest. Enter it into B1 and drag it down or click twice on the filling plus:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"_Folder\","."),"c:\",""),"_Folder","")