Deleting the first character of all the words starting with specific letters except for the first word in Excel

Question

I am trying to build a UDF that will look into a cell and delete the first character of all the words starting with the letters "El" except for the first word.

Value in cell A1 will be:

El agua, El asma, El arca, Elhambre, El aguila, Elements

In cell B1 where the UDF function will be called I want the result to be:

El agua, l asma, l arca, lhambre, l aguila, lements

My code is as follows:

    Function exceptFirst(MyString As String)
    Dim X As Long
    Dim Tempstr As String
    Tempstr = ""
    For X = 1 To Len(MyString)
        If Mid(MyString, X, 2) = "El" Then

            Tempstr = Tempstr

        Else

            Tempstr = Tempstr & Mid(MyString, X, 1)


        End If
    Next
    exceptFirst = Tempstr
    End Function

the code is doing the job of deleting all the "E"'s in the words starting with "EL" but it is not excluding the first occurrence in the first word. Your help is much appreciated and thanks in advance.

Answer 1

And another UDF, using Split & Join

Option Explicit
Option Compare Binary 'to ensure case sensitive
Function exceptFirst(S As String) As String
    Dim V As Variant
    Dim I As Long
    Dim bFirst As Boolean

bFirst = False
V = Split(S, ", ")

For I = LBound(V) To UBound(V)
    Select Case bFirst
        Case False
            If V(I) Like "El*" Then bFirst = True
        Case True
            If V(I) Like "El*" Then V(I) = Mid(V(I), 2)
    End Select
Next I

exceptFirst = Join(V, ", ")

End Function

EDIT I have assumed that you wanted the El to be case sensitive, both in the replacements and in the detection of which is the first instance. If that is not the case, merely change the Option Compare statement from Binary to Text