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
/Emplyoyees/Midwest/2010/annejones.xls
/Emplyoyees/West/2010/johnjones.xls
/Emplyoyees/East/2010/Suspended/jerryjones.xls

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
upobDaPlayaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tel2Commented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
        cel.End(xlToRight).ClearContents
    Next cel
End Sub
0
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
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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

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.
0
tel2Commented:
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.
0
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.
0
tel2Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.