CODEWITHSUNDEEP
stringvbaexceluser-defined-functions
excelDay
excelDay

Reputation: 35

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

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.

Upvotes: 3

Views: 189

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

Upvotes: 3

Vegard
Vegard

Reputation: 4917

And regex:

Sub RegexTest()
    Dim strInput As String, strPat As String
    Dim rX As New RegExp

    strPat = "(?:\s)(e)(?=l)"
    strInput = "El agua, Elhambre, El sol, elanore, bella"
    Debug.Print "Input: " & strInput
    Debug.Print "Expected output: El agua, lhambre, l sol, lanore, bella"

    With rX
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPat
    End With

    Debug.Print "Output: " & rX.Replace(strInput, " ")
End Sub

Which should give you

Input:           El agua, Elhambre, El sol, elanore, bella    
Expected output: El agua, lhambre, l sol, lanore, bella   
Output:          El agua, lhambre, l sol, lanore, bella

The pattern can be improved - I don't know why it captures the space before the e, when it's in a non-capturing group ... but in this case, the hack is to insert a space during the replace.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

This is what I have managed to build up, using the sample you have given:

Option Explicit

Function exceptFirst(MyString As String) As String

    Dim varArr      As Variant
    Dim cnt         As Long
    Dim result      As String

    varArr = Split(MyString, ", ")

    For cnt = LBound(varArr) To UBound(varArr)
        If cnt > 0 Then
            If UCase(Left(varArr(cnt), 2)) = "EL" Then
                result = result & Right(varArr(cnt), Len(varArr(cnt)) - 1) & ", "
            Else
                result = result & varArr(cnt) & ", "
            End If
        Else
            result = varArr(0) & ", "
        End If
    Next cnt

    exceptFirst = Left(result, Len(result) - 2)

End Function

Public Sub TestMe()

    Debug.Print exceptFirst("El agua, El asma, Elhambre, El aguila, cska, Elements")

End Sub

It abuses a bit the usage of Left() and Right() but the code provides pretty much what yu need. The tricky part is that it strips by , and on the loop it adds it back. At the end of the code, when it returns the string, it removes the last comma and the space: exceptFirst = Left(result, Len(result) - 2)

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

As Patrick suggested, you may use the Split function to achieve the desired output...

Function exceptFirst(ByVal MyString As String) As String
Dim i As Long
Dim Tempstr As String
Dim str() As String

str() = Split(MyString, ", ")

Tempstr = str(0)
For i = 1 To UBound(str)
    If LCase(Left(str(i), 2)) = "el" Then
        Tempstr = Tempstr & ", " & Right(str(i), Len(str(i)) - 1)
    Else
        Tempstr = Tempstr & ", " & str(i)
    End If
Next i
exceptFirst = Tempstr
End Function

Upvotes: 1

Related Questions