Forum Discussion

WJ876400's avatar
WJ876400
Helper 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
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    WJ876400 

     

    Using Power Query, you can try this

     

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


Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
Helper IV
6 years ago

Hi Anonymous 

 

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

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    WJ876400 

     

    Using Power Query, you can try this

     

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


  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi WJ876400 ,

    While writting this logic, I assumed that the Numbers would appear only at the end of the string.

    Is that correct?

     

    Thanks.

    • WJ876400's avatar
      WJ876400
      Helper IV
      6 years ago

      Hi Anonymous 

       

      Sorry there is a number at the front but i have solved the issue now thank you