CODEWITHSUNDEEP
excelvba
Harish
Harish

Reputation: 3483

How to get a particular part of a String

I am writing a macro in Excel where I need to get a substring from a String. It's 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 InStr but it's not useful. Can anyone help?

Upvotes: 7

Views: 92691

Answers (3)

kriss
kriss

Reputation: 24207

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

Upvotes: 2

Jojo Sardez
Jojo Sardez

Reputation: 8578

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

Upvotes: 2

Rune Grimstad
Rune Grimstad

Reputation: 36340

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.

Upvotes: 13

Related Questions