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,

    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?

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,

    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?

• excel
• vba

Share Improve this question Follow edited Nov 21, 2024 at 2:55 taller 19.1k22 gold badges88 silver badges2323 bronze badges asked Nov 21, 2024 at 2:35 FotoDJFotoDJ 35122 silver badges1212 bronze badges

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 2

How about a simple loop on column A where we check if the string starts with any of those, and if so, put N and add the rest back on?

Sub Replace_NX_NC_NL()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim originalText As String
    Dim modifiedText As String

    ' Set the worksheet to work on
    Set ws = ThisWorkbook.Sheets(1) ' Change the index or name as necessary
    
    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each cell in column A
    For i = 1 To lastRow
        originalText = ws.Cells(i, "A").Value
        modifiedText = originalText
        
        ' Check if the string starts with NL, NC, or NX
        If Left(originalText, 2) = "NL" Or Left(originalText, 2) = "NC" Or Left(originalText, 2) = "NX" Then
            ' Replace the first occurrence of NL, NC, or NX with N
            modifiedText = "N" & Mid(originalText, 3)
        End If
        
        ' Paste the modified text into column C
        ws.Cells(i, "C").Value = modifiedText
    Next i

End Sub

Use this formula:

=IF(SUM(--(LEFT(A5,2)={"NL","NC","NX"})),REPLACE(A5,1,2,"N"),A5)

Checks the start of the string and does a replace. Otherwise leave the original one.

Here is another way which doesn't use a loop

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        With .Range("C5:C" & lRow)
            .Formula = "=IF(OR(LEFT(A5,2)=""NL"", LEFT(A5,2)=""NC"", LEFT(A5,2)=""NX""), ""N"" & MID(A5,3,LEN(A5)-2), A5)"
            .Value = .Value
        End With
    End With
End Sub

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