Forum Discussion

WJ876400's avatar
WJ876400
Icon for Helper IV rankHelper IV
6 years ago
Solved

Slpit numbers from text

Hi

 

How do I split the number from the end of this text and have the value as a number. 

 

 

Thank you for any help

  • WJ876400 

     

    Using Power Query, you can try this

     

    Select Column>>>Go to TRANSFORM Tab>>Split Column>>BY Non digit to digit


5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi,

    This might not be the best of the solutions, but it should work.

    Number = 
    VAR v_OnePos = SEARCH( "1", Data[Column], 1, 999 )
    VAR v_TwoPos = SEARCH( "2", Data[Column], 1, 999 )
    VAR v_ThreePos = SEARCH( "3", Data[Column], 1, 999 )
    VAR v_FourPos = SEARCH( "4", Data[Column], 1, 999 )
    VAR v_FivePos = SEARCH( "5", Data[Column], 1, 999 )
    VAR v_SixPos = SEARCH( "6", Data[Column], 1, 999 )
    VAR v_SevenPos = SEARCH( "7", Data[Column], 1, 999 )
    VAR v_EightPos = SEARCH( "8", Data[Column], 1, 999 )
    VAR v_NinePos = SEARCH( "9", Data[Column], 1, 999 )
    
    VAR v_minOneandFour = MIN( MIN( v_OnePos, v_TwoPos ), MIN( v_ThreePos, v_FourPos ) )
    VAR v_minFiveandEight = MIN( MIN( v_FivePos, v_SixPos ), MIN( v_SevenPos, v_EightPos ) )
    VAR v_MinNine = MIN( MIN( v_minFiveandEight, v_minOneandFour ), v_NinePos )
    RETURN
    INT( IF( v_MinNine <> 999, RIGHT( Data[Column], (LEN( Data[Column] ) - v_MinNine) + 1  ), "0" ) )

     

    Thanks.

    Please accept this as a solution if it satisfies the requirement. Appreciate your Kudos. :)

    • WJ876400's avatar
      WJ876400
      Icon for Helper IV rankHelper IV

      Hi Anonymous 

       

      Thankyou for the response. I tried that but got an error which is below

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion

        WJ876400 

         

        Using Power Query, you can try this

         

        Select Column>>>Go to TRANSFORM Tab>>Split Column>>BY Non digit to digit