cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Slpit numbers from text

Hi

 

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

 

text.PNG

 

Thank you for any help

1 ACCEPTED SOLUTION

Accepted Solutions

@WJ876400 

 

Using Power Query, you can try this

 

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


split.png

Regards,
Zubair


View solution in original post

5 REPLIES 5
Solution Supplier
Solution Supplier

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

result.PNG

 

Thanks.

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

Hi @vaibhavdesai 

 

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

 

text2.PNG

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 

 

Using Power Query, you can try this

 

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


split.png

Regards,
Zubair


View solution in original post

Hi @vaibhavdesai 

 

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors