VBA Excel - find value within string and replace

[ArtyS20]

Hello:

I need to delete part of a string but given that the common character (semicolon) repeats multiple times, I'm unable to come with a formula/code that can be used in vba.

Column B contains a series of characters similar to this - ;2;1;3;2;321;1;TEST 2022;2;35047.309637; - where the length is variable. What I need to do is obtain the value within the last set of semicolon. In this example - 35047.309637 - will be the final value I want to see.

Column B is populated sometimes and some other times is blank. Range of rows varies and it is defined by Column A.

Any help is appreciated, thanks.

 

[Joe4]

Assuming that you want to start from row 2, try this code:

Sub SplitValues()

    Dim lr As Long
    Dim r As Long
    Dim arr() As String
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Get last row with data in column B
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows starting in row 2
    For r = 2 To lr
        If Cells(r, "B") <> "" Then
            arr = Split(Cells(r, "B"), ";")
            i = UBound(arr)
            Cells(r, "B") = arr(i - 1)
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Micron]

Won't that split every character in the string (except for ;'s) into separate cells?
I worked out the character removal portion, not the cell looping part, and came up with

Public Sub TestRemove(strIn As String)

strIn = Mid(strIn, InStrRev(strIn, ";", Len(strIn) - 1))
strIn = Replace(strIn, ";", "")
MsgBox strIn

End Sub

Called as: testremove ";2;1;3;2;321;1;TEST 2022;2;35047.309637;"

 

[Joe4]

Won't that split every character in the string (except for ;'s) into separate cells?

Not at all.
It will go through every populated cell in column B, and replace the contents with just the value of the last value between semi-colons in that cell.
Test and out and see for yourself!

 

[Micron]

Of course! i is always the count of elements, thus i-1 is always the last (of a zero based array). My mind was stuck on looping through an array, which has always been how I've used them.
You win the prize for that stroke of genius, and for having the solution with the most code lines!

 

[ArtyS20]

Assuming that you want to start from row 2, try this code:

Sub SplitValues()

    Dim lr As Long
    Dim r As Long
    Dim arr() As String
    Dim i As Long
   
    Application.ScreenUpdating = False
   
'   Get last row with data in column B
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through all rows starting in row 2
    For r = 2 To lr
        If Cells(r, "B") <> "" Then
            arr = Split(Cells(r, "B"), ";")
            i = UBound(arr)
            Cells(r, "B") = arr(i - 1)
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Thanks much your help.

I'm getting an error - stuck on this line of code: Cells(r, "B") = arr(i - 1)

Error: Subscript out of range

To me the code is correct and it is declaring all variables. What am I missing?

Thank you

 

[Micron]

At what point? First pass? Sometime after? What are the values of r and i at when it breaks (stops executing)? Perhaps one of them is zero.

 

[ArtyS20]

At what point? First pass? Sometime after? What are the values of r and i at when it breaks (stops executing)? Perhaps one of them is zero.

Got it. Some cells in Column B are blank. It happened to be the 1st one a blank cell. That's why it was failing.

It works perfectly though for the actual values. Exactly what I needed.

Thanks very much!

 

[Micron]

Not sure why If Cells(r, "B") <> "" Then would not have prevented the error, other than to say the cell must contain something, even if it's only a space character?
Maybe this if you're adapting the code?

arr = Split(Cells(r, "B"), ";")
   If Not IsEmpty(arr) Then
     i = UBound(arr)

 

[ArtyS20]

Not sure why If Cells(r, "B") <> "" Then would not have prevented the error, other than to say the cell must contain something, even if it's only a space character?
Maybe this if you're adapting the code?

arr = Split(Cells(r, "B"), ";")
   If Not IsEmpty(arr) Then
     i = UBound(arr)

Good catch! I would've been stuck on this forever. Indeed, there is a space character. I ended up adding the code to delete the space before the original code you sent:

Columns("B:B").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Working fine. Thanks for your help.