if cell contains substring remove the substring

[ajm]

folks,

I need to remove the title "Dr" from a list of names prior to publishing. Not all names have this title. I can find the substring within my specific range with

pos = InStr(nm1, "Dr ")

where nm1 contains a name. nm1 refers to the cell C9 on my spreadsheet.

if nm1 does not contain the substring "Dr ", pos will be zero. if it does contain the substring, pos will be greater than zero, so remove

If pos > 0 Then .... remove

I will then be saving the file using the string (now without Dr in it) as the name. Can i reuse the nm1 variable or do i need to create another?

so far i have

Sub SavePdf()

Dim ws As Worksheet
Dim nm1 As String
Dim nm2 As String
Dim pos As Integer
 

For Each ws In ActiveWorkbook.Worksheets
    ws.Select


nm1 = Range("C9")
nm2 = Trim(Range("A2"))

'pos = InStr(nm1, "Dr ")

   ' If pos > 0 Then nm3 = Replace("Dr ", 7, "")
    
    

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Temp\Rerun\" & nm1 & " CHQ " & nm2 & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

Next ws
 
End Sub

 

[Mary90]

Remove the second parameter in the Replace;

Sub SavePdf()

Dim ws As Worksheet
Dim nm1 As String
Dim nm2 As String
Dim pos As Integer
 


For Each ws In ActiveWorkbook.Worksheets
    ws.Select




nm1 = Range("C9")
nm2 = Trim(Range("A2"))


pos = InStr(nm1, "Dr ")


    If pos > 0 Then nm1 = Range("C9").Replace("Dr ", "")


'        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
'        Filename:="C:\Temp\Rerun\" & nm1 & " CHQ " & nm2 & ".pdf", _
'        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
'        IgnorePrintAreas:=False, OpenAfterPublish:=False


Next ws
 
End Sub

 

[ajm]

cheers Mary. I was labouring under the misapprehension that i had to specify the start position of the substring that i wanted removed.