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

L33

Board Regular
Joined
Jul 2, 2008
Messages
108
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...
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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))
 
Upvote 0
My solution:

Code:
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
 
  • Like
Reactions: L33
Upvote 0
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.)
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top