CODEWITHSUNDEEP
excelvba
Kai
Kai

Reputation: 43

How to identify and remove a single letter from a string in a cell?

I have a dataset of names in a column in Excel. However, only some but not all of the names have a letter attached to the end of it (e.g. John Doe A, Kai Jin, Johnny Desplat Lang B, etc).

Can anyone think of a method to remove the letter from the end of the name from each row, if it is there? Such that, using the example above, I will be left with: John Doe, Kai Jin, Johnny Desplat Lang, etc.

I am fairly familiar with VBA and Excel and would be open to trying anything at all.

Thank you for your help with this question! Apologies beforehand if this seems like an elementary question but I have no idea how to begin to solve it.

Upvotes: 1

Views: 91

Answers (2)

T.M.
T.M.

Reputation: 9948

Just for fun and in order to demonstrate another approach via the Filter() function:

Function ShortenName(ByVal FullName As Variant) As String
'Purpose: remove a single last letter
    Dim n: n = Split(FullName, " "): n = Len(n(UBound(n)))
    ShortenName = Left(FullName, Len(FullName) + 2 * (n = 1))
End Function

Explanation

Applying the Split() function upon the full name and isolating the last name token (via UBound()) allows to check for a single letter length (variable n).

The function result returns the entire string length minus 2 (last letter plus preceding space) in case of a single letter (the the condition n = 1 then results in True equalling -1). - Alternatively you could have coded: ShortenName = Left(FullName, Len(FullName) - IIf(n = 1, 2, 0))

Upvotes: 1

JvdV
JvdV

Reputation: 75850

"I am fairly familiar with VBA and Excel and would be open to trying anything at all."

If so, then this can be done with a simple formula if you wish to avoid VBA. With your value in A1:

=IF(MID(A1,LEN(A1)-1,1)=" ",LEFT(A1,LEN(A1)-2),A1)

If you must use VBA, I think the Like operator comes in handy:

Sub Test()

Dim arr As Variant: arr = Array("John Doe A", "Kai Jin", "Johnny Desplat Lang B")

For Each el In arr
    If el Like "* ?" Then 'Or "* [A-Z]" if you must check for uppercase alpha.
        Debug.Print Left(el, Len(el) - 2)
    Else
        Debug.Print el
    End If
Next

End Sub

enter image description here

Upvotes: 4

Related Questions