Replace part of a string with VBA

herman · Jan 5th 2022

Hello,

I'm working on a macro to partly delete strings like this : Mededeling: PAYCONIQ 6C328820EFFE1F7187A0762B FRITUUR DENISE Persoonlijke info: 6C328820EFFE1F7187A0762B

There might be more varying text before the word "mededeling"

The long alpha-numeric part appears twice every time but is otherwise unique.

This part : FRITUUR DENISE Persoonlijke info: 6C328820EFFE1F7187A0762B should completely be deleted.

I'm quite familiar with vba but this is a hard one for me. Thank you very much for helping me out .

Herman

dangelor · Jan 5th 2022

Possibly...

Code

Sub test()
    Dim rg As Range, v As Variant, i As Long
    
    'Assumptions:
        'one column of data in column 1
        'each line of data ends with long alpha-numeric part
        'long alpha-numeric part is duplicated in each line of data
        
    'Writes truncated data in column 2
    
    Set rg = Cells(1, 1).CurrentRegion.Columns(1)
    For i = 1 To rg.Rows.Count
        v = Split(rg.Cells(i, 1), " ")
        v = v(UBound(v))
        rg.Cells(i, 1).Offset(, 1) = Left(rg.Cells(i, 1), InStr(1, rg.Cells(i, 1), v) + Len(v))
    Next i
    
End Sub

Display More

herman · Jan 8th 2022

Hi Dangelor, works perfect and I completely understand your code.

Thank you very much for this fast and beautifully compact code!

Herman

dangelor · Jan 8th 2022

Glad it worked for you.

Replace part of a string with VBA

Participate now!

Share

Tags