cancel
Showing results for
Did you mean:
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.

Thank you for any help

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

Using Power Query, you can try this

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

Regards,
Zubair

5 REPLIES 5
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" ) )```

Thanks.

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

Helper III

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

Solution Supplier

Hi @WJ876400 ,

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

Is that correct?

Thanks.

Super User I

Using Power Query, you can try this

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

Regards,
Zubair

Helper III

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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