Find first Alpha Character from right side of a string

Kmac224

New Member
Joined
Jun 15, 2015
Messages
47
Office Version
  1. 2010
Platform
  1. Windows
Hi All,

I'm trying to use a formula to find the first alpha character from the right of a string.

For example, from the below, I'd expect "C" returned.

BABA US 09/21/18 C165
<colgroup><col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5888;"> <tbody> </tbody>

Thanks!!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
We can create our own function in VBA to do this. It would look something like this:
Code:
Function GetAlpha(myEntry As String) As String

    Dim i As Long
    Dim ln As Long
    Dim cr As String
    Dim ac As Integer
    
    ln = Len(myEntry)
    If ln > 0 Then
        For i = ln To 1 Step -1
            cr = Mid(myEntry, i, 1)
            ac = Asc(cr)
            If ((ac >= 65) And (ac <= 90)) Or ((ac >= 97) And (ac <= 122)) Then
                GetAlpha = cr
                Exit For
            End If
        Next i
    End If
    
End Function
Then, we can use it like any other Excel function.
So, if we had an entry in cell A1 that we wanted to pull the last letter out of, that formula would look like this:
=GetAlpha(A1)
 
Upvote 0
Here's another User-Defined Function you can try after you install the function.
Excel Workbook
AB
1BABA US 09/21/18 C165C
Sheet1



To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Function FirstAlphaFromRight(S As String) As String
Dim i As Long
For i = Len(S) To 1 Step -1
    If Mid(S, i, 1) Like "[A-Za-z]" Then
        FirstAlphaFromRight = Mid(S, i, 1)
        Exit Function
    End If
Next i
End Function
 
Upvote 0
Like "[A-Za-z]"
I keep forgetting about using "Like" like that in VBA. It certainly does make it shorter/simpler!
 
Upvote 0
Just as an alternative, there are formulas based on native Excel functions that could work, but they may have some restrictions based on your data. For example:

ABCDE
1BABA US 09/21/18 C165CCC

<tbody>
</tbody>
Sheet18

Worksheet Formulas
CellFormula
B1=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))))
C1=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D1{=MID(A1,AGGREGATE(14,6,SEARCH(CHAR(ROW(INDIRECT("97:122"))),A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1))))),1),1)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



The B1 formula assumes there is a space preceding the last letter.

The C1 formula assumes that there are only numbers following the last letter.

The D1 formula makes no assumptions, but it is an array formula.

Hope there's something useful in there.
 
Upvote 0
Just as an alternative, there are formulas based on native Excel functions that could work, but they may have some restrictions based on your data. For example:

A
B
C
D
E
1
BABA US 09/21/18 C165
C
C
C

<tbody>
</tbody>
Sheet18



Array Formulas
Cell
Formula
D1
{=MID(A1,AGGREGATE(14,6,SEARCH(CHAR(ROW(INDIRECT("97:122"))),A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1))))),1),1)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


The D1 formula makes no assumptions, but it is an array formula.

Hope there's something useful in there.

Your last formula ca be used normal, just ENTER (not as array formula)

and formula from C1 has missing one SUBSTITUTE
 
Last edited:
Upvote 0
Good catch.

My original C1 formula had all 10 SUBSTITUTEs, but something went wrong with the formatting when I posted it. Here's the right version:

=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))


I don't believe that you can leave off the CSE for the D1 formula. For example, try putting a C somewhere near the front of the A1 value. It gets found, but not the later C.
 
Last edited:
Upvote 0
Here is another normally-entered formula that should also work...

=RIGHT(TRIM(SUBSTITUTE(A1,LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99))),"")))
 
Upvote 0
Here is another normally-entered formula that should also work...

=RIGHT(TRIM(SUBSTITUTE(A1,LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99))),"")))
Doesn't seem to work for an all alpha entry like "abc"
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top