Test if Last Character in Column of Cells is Alpha Character

  • Thread starter Thread starter Benjamin
  • Start date Start date
B

Benjamin

How do I test if the last character in Column A is a Alpha Character
Sample cell: 0238:H601X or 0238:T0624
the numbers in the cell still will show up as Text format.
How can I do an excel formula or VBA to give me a true/false if the last
character is an alpha(letter)?
 
Try the below

Sub Macro()
If Right(Trim(Range("A1")), 1) Like "[A-Za-z]" Then
MsgBox "Alpha"
End If
End Sub

If this post helps click Yes
 
You can use not isnumeric()

If not isnumeric(Right(Trim(Range("A1")), 1)) Then

or

If isnumeric(Right(Trim(Range("A1")), 1)) Then


The trim is needed if there may be blanks at the end of the string.
 
An even simpler construction for the test...

Sub Macro()
If Range("A1") Like "*[A-Za-z]" Then
MsgBox "Alpha"
End If
End Sub

By the way, I removed the TRIM function call because that would mask the
fact that the string ended with something other than an alpha character.

--
Rick (MVP - Excel)


Jacob Skaria said:
Try the below

Sub Macro()
If Right(Trim(Range("A1")), 1) Like "[A-Za-z]" Then
MsgBox "Alpha"
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


Benjamin said:
How do I test if the last character in Column A is a Alpha Character
Sample cell: 0238:H601X or 0238:T0624
the numbers in the cell still will show up as Text format.
How can I do an excel formula or VBA to give me a true/false if the last
character is an alpha(letter)?
Click to expand...
Click to expand...
 
Yes; thereby can avoid the usage of RIGHT(); but hope the OP would want to
ignore any spaces at the end..

If this post helps click Yes
---------------
Jacob Skaria


Rick Rothstein said:
An even simpler construction for the test...

Sub Macro()
If Range("A1") Like "*[A-Za-z]" Then
MsgBox "Alpha"
End If
End Sub

By the way, I removed the TRIM function call because that would mask the
fact that the string ended with something other than an alpha character.

--
Rick (MVP - Excel)


Jacob Skaria said:
Try the below

Sub Macro()
If Right(Trim(Range("A1")), 1) Like "[A-Za-z]" Then
MsgBox "Alpha"
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


Benjamin said:
How do I test if the last character in Column A is a Alpha Character
Sample cell: 0238:H601X or 0238:T0624
the numbers in the cell still will show up as Text format.
How can I do an excel formula or VBA to give me a true/false if the last
character is an alpha(letter)?
Click to expand...
Click to expand...
Click to expand...
 
YES! that worked so good! Just what I needed. Thanks Jacob!

Jacob Skaria said:
Try the below

Sub Macro()
If Right(Trim(Range("A1")), 1) Like "[A-Za-z]" Then
MsgBox "Alpha"
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


Benjamin said:
How do I test if the last character in Column A is a Alpha Character
Sample cell: 0238:H601X or 0238:T0624
the numbers in the cell still will show up as Text format.
How can I do an excel formula or VBA to give me a true/false if the last
character is an alpha(letter)?
Click to expand...
Click to expand...
 
Just one follow up question. The formula, as Jacob has presented it to you,
will ignore all trailing blank spaces at the end of your text string... is
that acceptable to you?

--
Rick (MVP - Excel)


Benjamin said:
YES! that worked so good! Just what I needed. Thanks Jacob!

Jacob Skaria said:
Try the below

Sub Macro()
If Right(Trim(Range("A1")), 1) Like "[A-Za-z]" Then
MsgBox "Alpha"
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


Benjamin said:
How do I test if the last character in Column A is a Alpha Character
Sample cell: 0238:H601X or 0238:T0624
the numbers in the cell still will show up as Text format.
How can I do an excel formula or VBA to give me a true/false if the
last
character is an alpha(letter)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top