Extract all number of 6 digits in all records

[Heremion]

Hello!

I searched for a while how to fix my problem but none of the solutions works fine, so I come here hoping some new help

This is an example of string I got in my table :
417720;2;2003;431967;432071;432135;432071;432504;432135;432504;433695

I would like to extract all number of 6 digits in my string and have in return this
417720;2003;431967;432071;432135;432071;432504;432135;432504;433695 (the 2 was removed)

I tried with regex like this in R language

# 'dataset' contient les données d'entrée pour ce script
library("stringr")
pattern <- "(([0-9]{6})*[;]*)*"
cleanRegEx <- str_extract(dataset$myCol, pattern)
Anomalies <- within (dataset, {Regex= cleanRegEx})

But when it works, it extracts only le first number of 6 digits.

I also tried the str_extract_all but I got everytime some errors.

Do you have any idea to get what I expected please ?

Thanks for your time

 

[ExcelToDAX]

You don't need to utilize VBA. You can use Excel functions. Assuming your cell is in cell A1:

=LET(vals,TEXTSPLIT(A1,";"),TEXTJOIN(";",TRUE,FILTER(vals,LEN(vals)=6)))

2003 shouldn't be in your 6 digit output like you posted.

 

[Heremion]

Sorry for the misunderstanding. I don't use VBA, I use regex in R language in Power Query.

But maybe there is another solution ?

 

[ExcelToDAX]

Assuming your column is called Column1, you could do something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList(Text.Split([Column1],";"))),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "Custom.1", each LengthFunction([Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Custom", "Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Column1"}})
in
    #"Renamed Columns"

And the function LengthFunction is the following code:

(tbl as table) =>
let
    #"Added Custom" = Table.AddColumn(tbl, "Custom", each Table.FromList(Text.Split([Column1],";"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1"}, {"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Text.Length([Column1])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 6)),
    #"Removed Columns1" = Text.Combine(Table.ToList(Table.RemoveColumns(#"Filtered Rows",{"Custom"})),";")
in
    #"Removed Columns1"

 

[ExcelToDAX]

Sorry better route would be:

= Table.AddColumn(Source, "New Column", each Main4([Column1]))

and for Main4 function would be:

(col as text) =>
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList(Text.Split(col,";"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1"}, {"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Length", each Text.Length([Column1])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Length] = 6)),
    #"Removed Other Columns1" = Text.Combine(Table.ToList(Table.SelectColumns(#"Filtered Rows",{"Column1"})),";")
in
    #"Removed Other Columns1"

 

[Heremion]

Sorry better route would be:

= Table.AddColumn(Source, "New Column", each Main4([Column1]))

and for Main4 function would be:

(col as text) =>
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList(Text.Split(col,";"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1"}, {"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Length", each Text.Length([Column1])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Length] = 6)),
    #"Removed Other Columns1" = Text.Combine(Table.ToList(Table.SelectColumns(#"Filtered Rows",{"Column1"})),";")
in
    #"Removed Other Columns1"

Hello, thanks for your help.

If my column is named "defect", do I have to adapt your code like this ?

= Table.AddColumn(Source, "New Column", each Main4([defect]))

and for Main4 function would be:

(col as text) =>
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList(Text.Split(col,";"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1"}, {"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Length", each Text.Length([Column1])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Length] = 6)),
    #"Removed Other Columns1" = Text.Combine(Table.ToList(Table.SelectColumns(#"Filtered Rows",{"Column1"})),";")
in
    #"Removed Other Columns1"

or do I also have to replace in your function everywhere I find "Column1" ?

Thanks for your time

 

[GraH]

what about a custom function?

(str as nullable text) as nullable list =>//(str as nullable text, delim as text) as nullable list =>
let 
     List = Text.Split(str, ";") // List = Text.SplitAny(str, delim)
    ,IsLen6 = List.Select(List, each Text.Length(Text.Select(_,{"0".."9"}))=6)
in 
    IsLen6

An optional argument could be added so to render the delimiter flexible. Either a delimiter of choice, or even a list of potential delimiters.
Applied to the given example

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    Inv_fn = Table.TransformColumns(#"Changed Type",{{"String", Fn_6digits, type list}}), //Inv_fn = Table.TransformColumns(#"Changed Type",{{"String", each Fn_6digits(_,";,"), type list}}),
    #"Expanded String" = Table.ExpandListColumn(Inv_fn, "String")
in
    #"Expanded String"

Input

Output

 

[Heremion]

what about a custom function?

(str as nullable text) as nullable list =>//(str as nullable text, delim as text) as nullable list =>
let
     List = Text.Split(str, ";") // List = Text.SplitAny(str, delim)
    ,IsLen6 = List.Select(List, each Text.Length(Text.Select(_,{"0".."9"}))=6)
in
    IsLen6

An optional argument could be added so to render the delimiter flexible. Either a delimiter of choice, or even a list of potential delimiters.
Applied to the given example

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    Inv_fn = Table.TransformColumns(#"Changed Type",{{"String", Fn_6digits, type list}}), //Inv_fn = Table.TransformColumns(#"Changed Type",{{"String", each Fn_6digits(_,";,"), type list}}),
    #"Expanded String" = Table.ExpandListColumn(Inv_fn, "String")
in
    #"Expanded String"

Input
View attachment 102864
Output
View attachment 102863

Hi Grah,

your function seems to be called isLen6 but into your second block, I don't find any reference to this function, do I miss something ?

Thanks

 

[GraH]

Hi Grah,

your function seems to be called isLen6 but into your second block, I don't find any reference to this function, do I miss something ?

Thanks

Th function name is Fn_6digits, which is the name of the query in which I wrote the function.

 

[Heremion]

Thanks a lot! It works like I expected!