Issue with string separation in VBA

Question

I would appreciate your help with the following please.

I need to separate some data in VBA so it can be set as separate variables to be used in a file naming system.

I have the following code:

    Sub StripSlash1()
Dim strVal As String, strVal2 As String, strVal3 As String, strVal4 As String

'strVal = "jack\tom\rich\Nicolson\KingMcManaman"
'strVal = "L:\Pictures\A B C\A5 GROUP\A5 KHAKI\f"
strVal = "L:\Pictures\A B C\A5 GROUP\BPD"

    Cells(2, 5).Formula = strVal

    strVal2 = Right(strVal, InStr(strVal, "\") - 1)
    Cells(2, 6).Formula = strVal2

    strVal4 = Left(strVal, InStrRev(strVal, "\") - 1)
    Cells(2, 7).Formula = strVal4

    strVal3 = Right(strVal4, InStr(strVal4, "\") - 1)
    Cells(2, 8).Formula = strVal3


End Sub

The three strVal at the start are 3 different choices for data to run the code on to test the code. The number of \ may differ in different situations.

The result we need is:

Data Set 1 strVal2 = KingMcManaman strVal3 = Nicolson

Data Set 2 strVal2 = f strVal3 = A5 KHAKI

Data Set 3 strVal2 = BPD strVal3 = A5 GROUP

I would appreciate your input as I have been on this alday with no luck.

Regards,

Sam

Kazimierz Jawor · Accepted Answer · 2015-02-09 21:15:36Z

7

Please consider using Split function which does the following in your situation:

strVal = "L:\Pictures\A B C\A5 GROUP\BPD"
Dim arrVal As Variant
    arrVal = Split(strVal, "\")

to get part of the strVal you have to remember that arrVal is an array:

strVal2 = arrVal(UBound(arrVal))         'result: BPD
strVal3 = arrVal(UBound(arrVal)-1)       'result: A5 GROUP

and so on...

edited Feb 9, 2015 at 21:15

answered Mar 18, 2013 at 17:23

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2013-03-18 17:46:44Z

1

Sub Tester()

    Dim s, arr

    s = "aaa\bbb\ccc\d\eee"
    arr = Split(s, "\")

    With ActiveSheet.Cells(2, 5)
        .Value = s
        .Offset(0, 1).Resize(1, UBound(arr) + 1).Value = arr
    End With

End Sub

answered Mar 18, 2013 at 17:46

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

user2087008 Over a year ago

Thanks. I didn't use this as the earlier answer by Kaz Jaw worked perfect for me.

Collectives™ on Stack Overflow

Issue with string separation in VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related