Find first Alpha Character from right side of a string

[Kmac224]

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.

[TABLE="width: 121"]
<colgroup><col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5888;"> <tbody>[TR]
[TD="width: 161, bgcolor: transparent"]BABA US 09/21/18 C165[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!!

 

[Joe4]

We can create our own function in VBA to do this. It would look something like this:

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)

 

[JoeMo]

Here's another User-Defined Function you can try after you install the function.

Excel Workbook
	A	B
1	BABA US 09/21/18 C165	C
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.

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

 

[Joe4]

Like "[A-Za-z]"

I keep forgetting about using "Like" like that in VBA. It certainly does make it shorter/simpler!

 

[Eric W]

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
BABA US 09/21/18 C165	C	C	C

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet18​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]{=MID(A1,AGGREGATE(14,6,SEARCH(CHAR(ROW(INDIRECT("97:122"))),A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1))))),1),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.

 

[Ingolf]

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
BABA US 09/21/18 C165	C	C	C

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet18​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]D1
[/TH]
[TD="align: left"]{=MID(A1,AGGREGATE(14,6,SEARCH(CHAR(ROW(INDIRECT("97:122"))),A1,TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1))))),1),1)}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Eric W]

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.

 

[Rick Rothstein]

Here is another normally-entered formula that should also work...

=RIGHT(TRIM(SUBSTITUTE(A1,LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99))),"")))

 

[Joe4]

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"

 

[Ingolf]

Double post.