 # Splitting alpha from numeric

I have a field which is essentially numeric with the possibility of a one character suffix. There can be 1, 2, or 3 numbers followed possibly  by the alpha suffix

ex:      result

num   Suffix

2          2

3x        3           x

10      10

10w    10         w

114   114

115x  115        x

I would like to split this into two fields; one numeric, and one an alpha suffix field.  I'm totally at a loss as to how to do it.

5 Replies

# Re: Splitting alpha from numeric

@NorskGrandpa Please see the attached workbook for two working examples. One with regular formulae and one using Power Query.

# Re: Splitting alpha from numeric

Thank you so much for the very elegant solution.
And also for the quick response

# Re: Splitting alpha from numeric

Alternative formulas:

``````=IF(ISNUMBER(--RIGHT(A2)),A2,--LEFT(A2,LEN(A2)-1))
=SUBSTITUTE(A2,C2,"")``````

# Re: Splitting alpha from numeric

Another alternative would be this...

To extract Numbers:

``=IF(ISTEXT(A2),--LEFT(A2,LEN(A2)-1),A2)``

To extract Suffix character:

``=IF(ISTEXT(A2),RIGHT(A2),"")``

# Re: Splitting alpha from numeric

Considering your data this will be the best possible solution: • Formula in cell F1:

``=IF(ISNUMBER(E1),"",RIGHT(E1,1))``

• Formula in cell G1:

``=SUMPRODUCT(MID(0&E1, LARGE(INDEX(ISNUMBER(--MID(E1, ROW(INDIRECT("1:"&LEN(E1))), 1)) * ROW(INDIRECT("1:"&LEN(E1))), 0), ROW(INDIRECT("1:"&LEN(E1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(E1)))/10)``

N.B. Adjust cell references in the formula as needed.

I would like to suggest few UDFs (User Defined Functions), are VBA macro, which helps to extract Numbers as well Alphabets from any position regardless of their length.

``````Function PullNumbers(strText As String)

Dim i As Integer, strDbl As String

For i = 1 To Len(strText)

If IsNumeric(Mid(strText, i, 1)) Then
strDbl = strDbl & Mid(strText, i, 1)
End If
Next i

PullNumbers = CDbl(strDbl)
End Function``````

``````Function PullLetters(strText As String)

Dim x As Integer, strTemp As String

For x = 1 To Len(strText)

If Not IsNumeric(Mid(strText, x, 1)) Then
strTemp = strTemp & Mid(strText, x, 1)
End If
Next x

PullLetters = strTemp
End Function``````

How it works:

1. To get VB editor either press Alt+F11 or Right click Sheet Tab & from menu hit View Code.
2.  Now form Menu hit Insert then Module.
3. Copy & Paste theses codes.
4. Save the WB as Macro Enables *.xlsm.
5. Write formula in cell F1:

``=PullLetters(E1)``

6. Formula in cell G1:

``=PullNumbers(E1)``

You may adjust cell references in the formula as needed.