Macro to remove certain text string ONLY if comes at first

[ghoneim]

Hi,

I need a macro that helps me check a column of text against a list of given words, remove these words from column cells ONLY when they come at the very first of the cell

Example:
I need to remove the word "learn" from a list of course titles in a column (A2: A100) - only when "learn" show up in the beginning:

learn how to design robots ---------->how to design robots

While if "learn" show up in any other part (middle, or last word):

Explore and learn how to design robots --------> stays the same
Robots designs: Where to learn----------> stays the same


Any help with that?
Thanks in Advance

[gjcase]

How about this:

Sub Test()
Dim str As String
Dim C As Range

For Each C In Selection
    str = C.Value
    If UCase(Left(str, 6)) = "LEARN " Then C.Value = Right(str, Len(str) - 6)
Next C
End Sub

Requires you select the range to be operated on first. If you wanted, you could hard-wire that.

[ghoneim]

It works exactly the way I need!

Thanks a million gjcase

[ghoneim]

One more thing gjcase..
I wonder how would this code look like if -instead of removing "learn ", adding "ing" to it to become "learning"? Still when comes at first of sentence only
I'd appreciate your response..

Thanks a lot

[gjcase]

The above doesn't use a list; the one below does:

Sub Test()
Dim str As String
Dim C As Range
Dim wrdlist() As Variant
Dim i As Integer
Dim l As Integer


wrdlist = Range("D2:D6")


For Each C In Selection
    str = C.Value
    For i = 1 To UBound(wrdlist)
        l = Len(wrdlist(i, 1)) + 1
        If UCase(Left(str, l)) = UCase(wrdlist(i, 1)) & " " Then C.Value = Right(str, Len(str) - l)
    Next i
Next C

End Sub

You need to specify your list of words to be replaced in D2:D6, or revise the macro to point to the correct address.

[gjcase]

Here you are:

Sub Test()
Dim str As String
Dim C As Range
Dim wrdlist() As Variant
Dim i As Integer
Dim l As Integer


wrdlist = Range("D2:D6")


For Each C In Selection
    str = C.Value
    For i = 1 To UBound(wrdlist)
        l = Len(wrdlist(i, 1)) + 1
        If UCase(Left(str, l)) = UCase(wrdlist(i, 1)) & " " Then C.Value = Left(str,l) & "ing " & Right(str, Len(str) - l)
    Next i
Next C

End Sub

[gjcase]

Here you are:

Sub Test()
Dim str As String
Dim C As Range
Dim wrdlist() As Variant
Dim i As Integer
Dim l As Integer


wrdlist = Range("D2:D6")


For Each C In Selection
    str = C.Value
    For i = 1 To UBound(wrdlist)
        l = Len(wrdlist(i, 1)) + 1
        If UCase(Left(str, l)) = UCase(wrdlist(i, 1)) & " " Then C.Value = Left(str,l) & "ing " & Right(str, Len(str) - l)
    Next i
Next C

End Sub