How do I split the number from the end of this text and have the value as a number.
Thank you for any help
Go to Solution.
Using Power Query, you can try this
Select Column>>>Go to TRANSFORM Tab>>Split Column>>BY Non digit to digit
View solution in original post
This might not be the best of the solutions, but it should work.
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 )
INT( IF( v_MinNine <> 999, RIGHT( Data[Column], (LEN( Data[Column] ) - v_MinNine) + 1 ), "0" ) )
Please accept this as a solution if it satisfies the requirement. Appreciate your Kudos. 🙂
Thankyou for the response. I tried that but got an error which is below
Hi @WJ876400 ,
While writting this logic, I assumed that the Numbers would appear only at the end of the string.
Is that correct?
Sorry there is a number at the front but i have solved the issue now thank you
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.