3

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?

3
  • 1
    Are you saying c:\1_Folder\2_Folder\3_Folder exists as a field? EG, A1 value is c:\1_Folder\2_Folder\3_Folder ?
    – Dave
    Feb 17, 2014 at 14:57
  • you are correct - apologies for not including it in the initial question. (Should I edit the question? Is this the correct etiquette?)
    – RobN
    Feb 17, 2014 at 14:59
  • yes BobJim, usually it's fine to edit your question to add as much detail as you can to ensure the question is as clear as possible.
    – Dave
    Feb 17, 2014 at 21:10

5 Answers 5

4

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

enter image description here

13
  • Thank you dave, I was OK getting the Sub Procedure into a module and assigned to the Button.
    – RobN
    Feb 17, 2014 at 15:20
  • Cool - I wasn't being rude going into that detail, but your post said VBA beyond your skills so I thought it best to give too much detail than too little (plus it may help others) :)
    – Dave
    Feb 17, 2014 at 15:23
  • Not taken that way at all, thank you for your guidance! How did you create the script so quickly though!
    – RobN
    Feb 17, 2014 at 15:24
  • 1
    @BobJim , I've made an update, and updated the image. Is this what you mean?
    – Dave
    Feb 17, 2014 at 15:34
  • 1
    @BobJim, see the new edit and screen shot
    – Dave
    Feb 17, 2014 at 15:58
3
  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
7
  • This is quite manual operation for 158K folder paths. As the formula you have suggested would need to be updated as the numbers of columns produced by the Delimit function varies hugely. Thanks for the initial idea though.
    – RobN
    Feb 17, 2014 at 14:55
  • So you do or do not want VBA? Feb 17, 2014 at 15:09
  • VBA will be fine if only minor edits are required regarding the range of cells it should process. I didn't specify VBA as I wanted to explore the formula based options first, prior to creating a VBA tool.
    – RobN
    Feb 17, 2014 at 15:12
  • 1
    I put in my version of VBA, looking only for numbers following \ Feb 17, 2014 at 15:41
  • This doesn't seem to work.. The results concatenate. You need to add strFolder = "" after For Each c In Range("A:A")
    – Dave
    Feb 17, 2014 at 15:42
2

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

enter image description here

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+.

4
  • Can you use capture groups in VBA? I think that'd make things a little simpler.
    – Jerry
    Feb 17, 2014 at 17:11
  • @Jerry You mean a non-capturing group like my edit? (Feel free to suggest a better pattern)
    – nixda
    Feb 17, 2014 at 18:56
  • 1
    I'm not completely familiar with Excel's regexes, but you should be able to get the value of (\d+) directly. I did a search and I think this is a nice example (although the regex might be a little complex. You don't need the non-capture group, so just having "\\(\d+)" should work.
    – Jerry
    Feb 17, 2014 at 19:09
  • 1
    @Jerry This is short enough for me. Thanks for the hint
    – nixda
    Feb 17, 2014 at 19:35
0

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:

enter image description here

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

enter image description here

3
  • 2
    Care to elaborate what 'Flash Fill' is? Otherwise, I don't think this would qualify as an answer =/
    – Jerry
    Feb 17, 2014 at 20:23
  • @Jerry, more details added... can you tell me why my answer does not qualify as an answer? where im wrong? (just want to know my mistake to prevent repeating it again) Feb 17, 2014 at 20:46
  • 1
    Just saying that using 'Flash Fill' doesn't qualify as an answer. It's like answering to the question 'How do I get the first 1000 prime numbers?' and replying 'Use Sieve of Eratosthenes'. Now, if the user has never heard of it they would be just as clueless as they would have been when asking the question. Also, it might be worth noting that Flash Fill is available only in Excel-2013 as of now and you don't know what excel version the OP is using.
    – Jerry
    Feb 17, 2014 at 20:53
-1

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","")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .