Deleting Specific Capital Letters

[Scubamil]

I goofed! When I extracted First Names & Last Names from a string, it picked up the middle initial. How can I delete the last letter from a string ONLY if it's in caps? Here's an example of my data:

[TABLE="width: 89"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Anna[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[/TR]
[TR]
[TD]AnnaM[/TD]
[/TR]
[TR]
[TD]AnneB[/TD]
[/TR]
[TR]
[TD]AnneE[/TD]
[/TR]
[TR]
[TD]AnneG[/TD]
[/TR]
[TR]
[TD]Annette[/TD]
[/TR]
[TR]
[TD]AntonioL[/TD]
[/TR]
[TR]
[TD]April[/TD]
[/TR]
[TR]
[TD]AprilB[/TD]
[/TR]
[TR]
[TD]AprilD[/TD]
[/TR]
[TR]
[TD]AprilD[/TD]
[/TR]
[TR]
[TD]AprilM

[/TD]
[/TR]
</tbody>[/TABLE]
I could just run the formula again that split the First and last names but this seemed like a challenge I might need to know the answer to at some point. Thanks all! Enjoy the Board....

 

[Roxxien]

First : welcome on the forum

Second : Here is the logic that you are searching

Sub DeleteMiddleLetter()
Dim r As Range

For Each r In Range("A:A")
'If last letter equal to is Ucase version
If Right(r, 1) = Right(UCase(r), 1) Then
    'Delete it
    r = Left(r, Len(r) - 1)
End If


Next
End Sub

 

[mrhstn]

Using a function

=IF(ISNUMBER(MATCH(true, EXACT(RIGHT(A2, 1), CHAR(ROW($65:$90))), 0)), LEFT(A2, LEN(A2)-1), A2)

Which will need entered using Ctrl+Shift+Enter

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub RemoveLastUpperCaseLetter()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(@="""","""",IF(EXACT(RIGHT(@),UPPER(RIGHT(@))),LEFT(@,LEN(@)-1),@))", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[mrhstn]

Think about the formula method, it could be simplified to...

=LEFT(A2, LEN(A2)-ISNUMBER(MATCH(TRUE, EXACT(RIGHT(A2, 1), CHAR(ROW($65:$90))), 0)))

IF function eliminated.

Regards

 

[Scott Huish]

As a formula:

=LEFT(A1,MAX(1,LEN(A1)-EXACT(RIGHT(A1),UPPER(RIGHT(A1)))))