How do I remove everything to the left of a specific character using VBA?

Question

So my question is this, how would I go about creating a VBA Macro where it will automatically cut the string after the 6th _? What I mean by this is the following:

As you can see it cuts everything prior of the 6th _ this is what I want to accomplish with the VBA macro. The reasoning behind the macro is due to other variances that I'll be adding in later for further automation. I know how to write it with an excel formula like so:

=RIGHT(SUBSTITUTE(A16,"_",CHAR(10),6),LEN(A16)-FIND(CHAR(10),SUBSTITUTE(A16,"_",CHAR(10),6),1)+1)

But I'm not sure how to write this in VBA format for it to work properly. I have tried to mimic an example from a website I found and this only works for the first underscore, I've tried to modify it so it would work correctly but keep running into errors. Here is the code I used:

Sub Test()

Dim K As Long
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row

For K = 2 To LR
 Cells(K, 2).Value = Right(Cells(K, 1).Value, Len(Cells(K, 1)) - InStr(1, Cells(K, 1).Value, "_"))

Next K

End Sub

Any help would be awesome, and if any clarification is needed I can certainly provide.

Possible duplicate of [How to extract file name from path?](https://stackoverflow.com/questions/1743328/how-to-extract-file-name-from-path) — Selkie, Nov 01 '19 at 19:00
You can also replicate any Excel formula in vba by using (in this case a lot!) of worksheetfunction. — Selkie, Nov 01 '19 at 19:02

JNevill · Answer 1 · 2019-11-01T19:42:16.730

1

Using a UDF:

Function TruncateString(strIn As String) As String
     Dim words As Variant, wordsout As Variant
     words = Split(strIn, "_")

     ReDim wordsout(0 To UBound(words) - 6)
     For i = 6 To UBound(words)
        wordsout(i - 6) = words(i)
     Next
     TruncateString = Join(wordsout, "_")
End Function

You can use that as a formula in your workbook after saving it in a new module like =TruncateString(A1)

edited Nov 01 '19 at 19:42

answered Nov 01 '19 at 19:07

JNevill

46,980
4
38
63

Ohhhh... yeah.. "To the left". Will rewrite – JNevill Nov 01 '19 at 19:11

BigBen · Accepted Answer · 2019-11-01T19:42:50.320

1

I think using the limit argument of Split here might work nicely.

Function StripAfter(ByVal txt As String, ByVal delimiter As String, ByVal occurrence As Long) As String
    Dim x As Variant
    x = Split(expression:=txt, delimiter:=delimiter, limit:=occurrence + 1)

    StripAfter = x(UBound(x))
End Function

Called as a UDF: =SplitAfter(A1,"_",6).

EDIT:

With your current code, change:

Cells(K, 2).Value = ...

to

Cells(K, 2).Value = StripAfter(Cells(K, 1).Value, "_", 6)

edited Nov 01 '19 at 19:42

answered Nov 01 '19 at 19:27

BigBen

46,229
7
24
40

Is there a way to input this into a button and do the entire column? Or would that be a different formula altogether? (I haven't messed with VBA for a while so I'm a little rusty) – Maykid Nov 01 '19 at 19:41
Pretty easy to incorporate into your current code, see the edit. – BigBen Nov 01 '19 at 19:43
You are welcome, glad to help. `Split` is the best. – BigBen Nov 01 '19 at 19:48

How do I remove everything to the left of a specific character using VBA?

2 Answers2