Forum Discussion

NorskGrandpa's avatar
NorskGrandpa
Copper Contributor
Apr 04, 2021

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   S...
excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Apr 04, 2021

    Riny_van_Eekelen 

    Alternative formulas:

    =IF(ISNUMBER(--RIGHT(A2)),A2,--LEFT(A2,LEN(A2)-1))
=SUBSTITUTE(A2,C2,"")
  • NorskGrandpa's avatar
    NorskGrandpa
    Copper Contributor
    Apr 04, 2021
    Thank you so much for the very elegant solution.
    And also for the quick response
    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Apr 04, 2021

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

       

Resources