CODEWITHSUNDEEP
excelvba
Maykid
Maykid

Reputation: 517

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

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: Example

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.

Upvotes: 0

Views: 1277

Answers (2)

BigBen
BigBen

Reputation: 50162

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).

enter image description here

EDIT:

With your current code, change:

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

to

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

Upvotes: 1

JNevill
JNevill

Reputation: 50308

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)

Upvotes: 1

Related Questions