• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

How do I use the InStr to extract text between slash signs

I have a Excel file that within one column tells me all the files we have on our LAN.  I am only interested in extracting out the folder names from each cell value and placing the result into multiple columns.  How do I within Excel VBA using the InStr or other method to evaluate the cell with the file information and extract all the individual folder names and place the result in individual columns (1st Path 2nd Path, 3rd Path,etc.)  The folder structure does not go beyond 5 deep.

Current Spreadsheet

Result I need
Row 1st Path       2nd Path     3rd Path       4th Path
1       Employees Midwest       2010              -
2       Employees West             2010              -
3       Employees East               2010             Suspended
3 Solutions
You could select the cells which contain the current data, and use "Text to Columns", specifying '/' as the delimiter.  Let me know if you don't know how to use "Text to Columns".  Probably under the Data menu.

Is this for one-off use, or regular?

What version of Excel are you using?
Saqib Husain, SyedEngineerCommented:
Try this macro
change the 4th line to your range

Sub getpathchain()
    Dim cel As Range
    Dim rng As Range
    Set rng = Range("A2:A200")
    rng.TextToColumns Destination:=rng, DataType:=xlDelimited, Other:=True, OtherChar:="/", _
        FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
    For Each cel In rng
    Next cel
End Sub
upobDaPlayaAuthor Commented:
Sorry..I should have mentioned its Excel 2010.  I will try both of the above tomorrow and tell you how I get along...thx
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

upobDaPlayaAuthor Commented:
Oh and its a 1-time task...
[ fanpages ]IT Services ConsultantCommented:

Here is another method:

Public Sub Q_28673381()

  Dim objCell                                           As Range
  Dim vntSplit                                          As Variant
  For Each objCell In Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
      vntSplit = Split(Mid$(objCell, 2), "/")
      Range(objCell.Address, objCell.Offset(, UBound(vntSplit) - 1)) = vntSplit
  Next objCell
  Set vntSplit = Nothing
  Set objCell = Nothing
End Sub

Open in new window

I presumed the spelling of "Emplyoyees" within the first list should have been "Employees" (as it is within the required layout of the data), so I have not accounted for changing this during the transformation.

However, this can be added to the above code very easily, if required.

Also, I assumed the data began in row 1 (i.e. cell [A1]).

If this is not the case, & the first row is 2, for example, change the line with the explicit reference "A1" to "A2" (or as required):

For Each objCell In Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
...will become...
For Each objCell In Range("A2", Cells(Cells.Rows.Count, "A").End(xlUp))

This said, ssaqibh's "TextToColumns" method will execute much faster than the row-by-row loop approach here.
Hi again upobDaPlaya,

If you're using my solution, you can delete the source column afterwards, if it's no longer needed.

Do you really need those '-' (dash) characters where there's no folder at that level?  If so, after doing the "Text to Columns", you could simply:
- Select all the path cells in one go.
- Under Home > Editing > Find & Select > Replace, clear anything from the "Find what" field, and put a dash in the "Replace with" field, then click "Replace All".
- Check the results.

But one problem, which I've just noticed, with my solution, is it includes the spreadsheet.xls filenames in the path columns output.  But you can resolve this by doing a replacement before dealing with the '-' issue, like this:
- Select all the cells that might contain .xls filenames in one go.
- Under Home > Editing > Find & Select > Replace, put *.xls in the "Find what" field, and clear the "Replace with" field, then click "Replace All".
- Check the results.
The above will work only if none of your folder names contain the characters '.xls' though.
upobDaPlayaAuthor Commented:
Hi tel2 I used your solution, but I did fool around with the other 2 solutions for my own knowledge and was able to get them to work.  Hopefully I was fair with the grading and I appreciate everyone's help.
Thanks for the points, upobDaPlaya.  Glad it worked for you.

Very fair grading, (in my totally unbiased opinion).    8)

Good to see you learned something from the other experts' solutions, so they weren't wasted.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now