Splitting Columns by Numbers or Characters

[Ken]

Is there a way to split columns based on a number, with the number being any number from 0 to 9?

 

[Pecoflyer]

Please provide an example sheet ( no pics pse) of what you are trying to achieve. Thx

 

[bdarbonneau]

You could use a this function:

Splitter.SplitTextByAnyDelimiter( List.Transform({0..9},each Text.From(_)))(InputText)

 

[Ken]

You could use a this function:

Splitter.SplitTextByAnyDelimiter( List.Transform({0..9},each Text.From(_)))(InputText)

 

[Ken]

bdarbonneau, thank you. Since I am inexperienced with Power Query, I have two questions:

1. How do I use this as a function? Do I just insert the code into the Advanced Editor?
2. Is the line of code you provided complete or do I have to insert anything into Text.From or replace InputText.

Also, does the same code apply for splitting a column with any letter, lowercase or uppercase?

 

[bdarbonneau]

bdarbonneau, thank you. Since I am inexperienced with Power Query, I have two questions:

1. How do I use this as a function? Do I just insert the code into the Advanced Editor?
2. Is the line of code you provided complete or do I have to insert anything into Text.From or replace InputText.

Also, does the same code apply for splitting a column with any letter, lowercase or uppercase?

In the view tab of the query editor, make sure the box 'Formula bar' is ticked, so as to be able to diectly edit the code that PQ creates.
Start by using the UI: Split Column -> Split by delimiter -> colon. This will generate a step named 'Split Column by Delimiter'. Select it.
The code in the formula bar should look like:

= Table.SplitColumn(#"Changed Type","ColumnA",Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),{"ColumnA.1", "ColumnA.2"})

Then edit this line as to have the code below:

= Table.SplitColumn(#"Changed Type","ColumnA",Splitter.SplitTextByAnyDelimiter( List.Transform({0..9},each Text.From(_))),{"ColumnA.1", "ColumnA.2"})