Extract text from string that matches a defined pattern (Power Query)

[L33]

Hi,

I need to use Power Query (in Excel) to extract from a long list of free text strings any instances of a 8-digit number that begins with a 4. This could be anywhere within the free text or no where. I need a new column that extracts that number, or returns null (and, in what would hopefully be incredibly rare event, just one instance when multiple matches are found).

Is there a simple way that I've missed? I have a nagging feeling that there is.

Any assistance gratefully received...

 

[krrishkrsna]

i think below code solves your problem

place below lines after Let and before In in power query




CharsToRemove = List.Transform({1..45,47,58..126}, each Character.FromNumber(_)),
#"xtractNum" = Table.AddColumn(#"Changed Type","Nums only", each Text.Remove([Column1],CharsToRemove)),


NumsToRemove = List.Transform({1..64,91..96,123..255}, each Character.FromNumber(_)),
#"xtractText" = Table.AddColumn(#"xtractNum","Char only",each Text.Remove([Column1],NumsToRemove))

 

[MarcelBeug]

My solution:

let
    Source = Table1,
    AddedTextExceptLast7Positions = 
        Table.AddColumn(Source, 
                        "TextexceptLast7Positions", 
                        each if Text.Length([String])<8
                             then null 
                             else Text.Start([String],Text.Length([String])-7)),
    AddedPositionsOf4s = 
        Table.AddColumn(AddedTextExceptLast7Positions,
                       "PositionsOf4s", 
                       each if [TextexceptLast7Positions] = null 
                            then {} 
                            else Text.PositionOf([TextexceptLast7Positions],"4",Occurrence.All)),
    Added8PositionsStartingWith4 = 
        Table.AddColumn(AddedPositionsOf4s, 
                        "8PositionsStartingWith4", 
                        (This) => if List.IsEmpty(This[PositionsOf4s]) 
                                  then {} 
                                  else List.Transform(This[PositionsOf4s], each Text.Middle(This[String],_,8))),
    // true or false is added to each list element, resulting in a list of lists {{string, true/false},{string, true/false}, etcetera}
    AddedCheckIfAllDigits = 
        Table.AddColumn(Added8PositionsStartingWith4, 
                        "CheckIfAllDigits", 
                        (This) => if List.IsEmpty(This[8PositionsStartingWith4]) 
                                  then {} 
                                  else List.Transform(This[8PositionsStartingWith4], each {_, List.AllTrue(List.Transform(Text.ToList(_), each _ >= "0" and _ <= "9"))})),
    // In this step, _{1} is the second element of each nested list, so true or false
    AddedSelection = 
        Table.AddColumn(AddedCheckIfAllDigits, 
                        "Selection", 
                        each List.Select([CheckIfAllDigits], each _{1})),
    AddedResult = 
        Table.AddColumn(AddedSelection, 
                        "Result", 
                        each if List.IsEmpty([Selection]) 
                             then null 
                             else [Selection]{0}{0}),
    RemovedColumns = 
        Table.RemoveColumns(AddedResult,
                            {"TextexceptLast7Positions", "PositionsOf4s", "8PositionsStartingWith4", "CheckIfAllDigits", "Selection"})
in
    RemovedColumns

 

[L33]

Thanks very much MarcelBeug - works perfectly. (krrishkrsna - thanks your reply too, didn't quite give me what I needed, but it's a handy piece of code I'll keep too.)