Splitting alpha from numeric

Copper Contributor

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

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

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

@Riny_van_Eekelen 

Alternative formulas:

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

@NorskGrandpa 

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),"")

 

@NorskGrandpa 

 

Considering your data this will be the best possible solution:

 

Rajesh-S_0-1617518441209.png

  • 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.