Search code examples
excelvba

VBA loop to search and replace first two characters of the string first occurrence only

If the string in cells of column A starts with NL, NC or NX I would like to replace it with letter N but only first occurrence of NL, NC, NX in each string and paste it into column C, image explains final result,

enter image description here

    Sub Replace_NX_NC_NL()


    Dim lr As Long
    Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
 
    Range("C5").Select
    ActiveCell.Formula = "=SUBSTITUTE(A5, ""NC"", ""N"",1 )"
    Selection.AutoFill Destination:=Range("C5:C" & LastRow)
   
    Range("C5:C" & LastRow).Select
    Selection.Copy
    Range("C5:C" & LastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues
        
    
    End Sub

I was able to start code to replace NC but next step is a mystery to me...anybody can help?

Solution

  • Please expand the keyword list KEYS as needed and check if the first two characters of cells in column A is included in the list.

    Microsoft documentation:

    InStr function

    Sub Replace_NX_NC_NL()
    Dim lr As Long, s As String
    Dim LastRow As Long
    Const KEYS = "NL,NC,NX"
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For lr = 5 To LastRow
        s = Left(Cells(lr, 1), 2)
        If InStr(1, KEYS, s, vbTextCompare) > 0 Then
            Cells(lr, 3) = "N" & Mid(Cells(lr, 1), 3)
        Else
            Cells(lr, 3) = Cells(lr, 1)
        End If
    Next
End Sub