How to get a particular part of a String

Question

I am writing a macro in Excel where I need to get a substring from a String. Its like this.

~/tester/test/hai/bye
~/stack/overflow/hai/bye

In the above cases I need to take the String tester from the first one and stack from the second one.I tried using the Instr but its not useful.Can anyone help this?

Answer 1

You can do this using the InStr and Mid functions. Use the InStr function to find the occurrences of the / and then use Mid to get the part of the string that you are interested in.

Try this:

Function ExtractFirstPartOfPath(path as String) as String

  Dim first, second as Integer

  first = InStr(path, "/")
  second = InStr(first + 1, path, "/")

  ExtractFirstPartOfPath = Mid(path, first + 1, second - first - 1)

End Function

This function will produce the desired results.

Answer 2

Try this:

Sub Macro1()
    Dim text As String, result As String
    text = "~/tester/test/hai/bye"
    result = Mid(text, 3, InStr(3, text, "/") - 3)
    'MsgBox is for demo only
    MsgBox result
End Sub

Answer 3

As far as I know there is no regex in Excel, you have to do what you want "by hand".

You can do it using Instr as others showed.

Below another solution using Split (this function is available for Excel 2000 and above)

Function ExtractFirstPartOfPath(path as String) as String

    Dim parts
    parts = Split(path, "/")
    ExtractFirstPartOfPath = parts(1)

End Function