Extract Numbers/ Text only from sentences

I saw similar problem posted in Power BI Community and thought this is worthy of a post
Here is the sample data

Countries+DialCodes
China86,India91,United States1,Indonesia62
Brazil55,Pakistan92,Bangladesh880,Nigeria234
Russia7,Japan81,Mexico52,Philippines63

And our objective is to extract Countries and Dial Codes in separate Columns
So here is the final desired output

Easy Peasy …. We can add a new Custom Column from the Query Editor and use the Text.Select “M” function to extract the Countries as follows

Similarly to get the “Codes” Column we can use following Custom Column

THE REAL CHALLENGE

The above problem was made easy because the delimiter i.e comma “,” already existed in the data.

What if our data looked like this and we need the same desired output as above

Countries+DialCodes
China86India91United States1Indonesia62
Brazil55Pakistan92Bangladesh880Nigeria234
Russia7Japan81Mexico52Philippines63

If we use the above Custom Columns using Text.Select we will get the following result

Our challenge is to insert a delimiter/comma between Countries and their Codes for which we can not use Text.Select. We have to use a different approach

In this case our first step will be to create 2 lists….One list is the list of characters to remove i.e. numbers from 0 to 9 and other list containing replacement for each of these numbers i.e. a delimiter (|)


Go to the Advanced Editor and create these 2 lists as follow

CharsToRemove = {"0".."9"},
CharToReplace=List.Repeat({"|"},List.Count(CharsToRemove)),

Our next step is to convert each Text i.e. “Countries + Dial Codes” into a list of individual characters using Text.ToList function

Then we can replace the “number” characters in that list with a delimiter of our choice (in this example “|”) using List.ReplaceMatchingItems function

Then we can combine the list back to Text again

So the Code for new Custom Column for above step and the resulting output looks like this

=Text.Combine(List.ReplaceMatchingItems(
               Text.ToList([#"Countries+DialCodes"]),
                List.Zip({CharsToRemove,CharToReplace})
                                       )
              )

Next Step is to split text into a list using delimiter (i.e. “|”) >>> remove the null values in the list>>>Recombine the list using Text.List

So the Code for new Custom Column for above step and the resulting output looks like this

Text.Combine(
         List.Distinct(
          List.RemoveItems(Text.Split([Custom],"|"),
                 {null,""})
                       )
             ,   
           ",")


Click here to download the Power BI file and see and play with the above examples