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

L33

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

Board Regular
Joined
Jan 31, 2009
Messages
65
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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
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

L33

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

Forum statistics

Threads
1,082,177
Messages
5,363,591
Members
400,753
Latest member
Lizanz

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top