search & replace macro

Hi, I'm using search & replace in a macro but only want to replace the text if it is in the first 3 letters within the cell. So, for example, I currently have;

Selection.Replace What:="DS", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

This is to delete the text "DS" but obviously it's deleting DS anywhere within the text rather than just at the beginning of the text.

any help much appreciated.

Liam

Hi LiamF

maybe something like

Option Explicit
Sub Tester()
Dim c
Dim RegEx As RegExp
    Set RegEx = New RegExp
    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "^DS"
  For Each c In Selection
        c.Value = RegEx.Replace(c.Value, "")
    Next
End With
Set RegEx = Nothing
End Sub

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Hi Shaner73
Doesnt the code will still replace DS any where in the text?

Hmmm. same problem with samples
DSAAAA
AADSAA
AAAADS

Hi ajaykgarg
No that doesnt work

Sub Tester()
Dim c
Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "^DS"
  For Each c In Selection
        c.Value = RegEx.Replace(c.Value, "")
    Next
End With
Set RegEx = Nothing
End Sub

Perhaps something like this

Sub tester()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If Left(c, 3) Like ("DS?") Or Left(c, 3) Like ("?DS") Then
          c.Replace What:="DS", Replacement:=""
    End If
    Next c
End Sub

HTH

Alf