search and replace parts of a string

[CL HND]

Hoping someone can help me make my macro run more efficiently. I am wanting to search for a string (code) in a specific column. Once I find it, I want to replace it with the correct text.

sample code: oc_L2_5_4
text replacement: Online Content, Lesson 2-5 p. 4

The underscores are used to separate the different types of data but I am unsure how to search for an underscore character, replace it with other (different) characters but keep the included values.

Many thanks for any info!


Sub sample()
Dim cell As Range
Dim lessonnum As Integer
Dim pagenum As Integer

For Each cell In Range("D1:D500")
cell.Replace What:="ocs_L", Replacement:="Online Content, Lesson "
Next cell

For Each cell In Range("D1:D500")
For lessonnum = 1 To 20 'lesson number range
For pagenum = 1 To 700 'page number range
cell.Replace What:="_" & lessonnum & "_" & pagenum, Replacement:="-" & lessonnum & " p. " & pagenum
Next pagenum
Next lessonnum
Next cell
End Sub

 

[AlphaFrog]

Sub sample()

    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Range("D1:D500").Replace What:="oc_L", Replacement:="Online Content, Lesson ", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
        
    For i = 1 To 20
        Range("D1:D500").Replace What:="_" & i & "_", Replacement:="-" & i & " p. ", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Next i
    
    Application.ScreenUpdating = True
        
End Sub

I'm not sure if you are replacing oc_L as your example indicates or ocs_L as your code indicates. I used oc_L in my code.

 

[CL HND]

Yes, the search was for oc_L.

Works like a charm in a fraction of the time! Thanks a ton!