find the first instance of a specif character and get the left value using vba

Question

How to find the first instance of a specif character and get the left value using vba

a= "\abc\123\abc\test\test123\123\1248\1234\Dec 03"

I need the output as "\abc\123\abc\test\test123\123\1248\1234", that is it should detect the first \ and get remaining string from the left using VBA.

Thank you.

I need complete string before the first "\". In my example the original string is "\abc\123\abc\test\test123\123\1248\1234\Dec 03" I need the result like "\abc\123\abc\test\test123\123\1248\1234" — Rick, Dec 07 '18 at 16:17
The first \ is \abs. Then *string before it* doesn't make a sense. — JohnyL, Dec 07 '18 at 16:18
first "\" from the back side. in my example last "\" is present near '\Dec 03" — Rick, Dec 07 '18 at 16:22

score 2 · Accepted Answer · answered Dec 07 '18 at 16:24

2

Sub str()
    Dim a As String
    a = "\abc\123\abc\test\test123\123\1248\1234\Dec 03"

    Dim ret As String
    ret = Left(a, InStrRev(a, "\") - 1)

    Debug.Print ret
End Sub

answered Dec 07 '18 at 16:24

Scott Craner

148,073
10
49
81

Nice - OP did say "find a specific character", so maybe add a parameter to the sub that lets the user define that character? But that's not mentioned anywhere by OP in posts or comments, just a thought! – BruceWayne Dec 07 '18 at 16:27

score 0 · Answer 2 · answered Dec 07 '18 at 16:24

I think you're mis-stating your goal. It looks like you want to keep the entire string, except the last part as delimited by a \.

This formula should work (thanks to @Tobin):

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)

For VBA, you can use:

Range("B1").FormulaR1C1 = _
    "=LEFT(RC[-1],FIND(""@"",SUBSTITUTE(RC[-1],""\"",""@"",LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],""\"",""""))))-1)"

score 0 · Answer 3 · answered Dec 07 '18 at 16:33

Make sure that the string you want to modify appears in Sheet1.Range("A1").
The result would appears at Sheet1.Range("A2").

Try:

Option Explicit

Sub test()

    Dim NoCh As Integer
    Dim i As Long, LP As Long
    Dim str As String

        str = Sheet1.Range("A1").Value
        NoCh = Len(str)


        For i = NoCh To 1 Step -1
            If Mid(str, i - 1, 1) = "\" Then
                LP = i
                Exit For
            End If

        Next i

        Sheet1.Range("A2").Value = Left(str, LP - 2)

End Sub

find the first instance of a specif character and get the left value using vba

3 Answers3