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

Highlighted
Helper III

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

Highlighted
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.

Highlighted
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

Highlighted
Helper III

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

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

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