Pull all 8 digit numbers from column

[btwice]

Hi,

I have a table that has a column of comments that can contain more than one 8 digit number. For every value in this column I need to pull every 8 digit number that is there, often separated by varying delimiters or no delimiter at all with inconsistencies in the data, but the number will always contain 8 digits, and will always start with a 3 or 4, never start with any other number.

How would I do this in power query to put values that have more than one 8 digit number in one cell with a comma delimiter which I can then use to split into rows? See below for a few examples of what I would like the result to be for multi 8 digit numbers with the "comment" column on the left, and resulting column on the right.

46353865 = 46353865
Line 137 = null
31540572_46403933_31597936 = 31540572,46403933,31597396
35544223 and 35601630 = 35544223,35601630

 

[PeteWright]

MrExcel_2025-03.xlsm
	A	B	C	D	E	F
10	46353865					46353865
11	Line 137					null
12	31540572_46403933_31597936					31540572,46403933,31597936
13	35544223 and 35601630					35544223,35601630
003

Cell Formulas
Range	Formula
F10:F13	F10	= IFERROR(TEXTJOIN(",",TRUE,REGEXEXTRACT(A10, "(\d{8})",1,1)),"null")

 

[alex78]

Hi @btwice,

let
A = Source, // previous step
B = {"Column1", "Digits"}, // "comment" column name & "extracted" column name
C = Table.SplitColumn(A, B{0}, (x)=> {x, 
[ 
a = Table.FromColumns({{null} & Text.ToList(Text.From(x))},{"x"}), 
b = Text.Combine(Table.Group(a, "x", {"y", each if List.Count([x])>=8 then Text.Select(Text.Combine([x]), {"0".."9"}) else null}
    ,0, (x,y)=> Byte.From(not List.ContainsAny({y},{"0".."9"})))[y],",")
]
[b]}, B) in C

Regards,

 

[btwice]

Hi @btwice,

let
A = Source, // previous step
B = {"Column1", "Digits"}, // "comment" column name & "extracted" column name
C = Table.SplitColumn(A, B{0}, (x)=> {x,
[
a = Table.FromColumns({{null} & Text.ToList(Text.From(x))},{"x"}),
b = Text.Combine(Table.Group(a, "x", {"y", each if List.Count([x])>=8 then Text.Select(Text.Combine([x]), {"0".."9"}) else null}
    ,0, (x,y)=> Byte.From(not List.ContainsAny({y},{"0".."9"})))[y],",")
]
[b]}, B) in C

Regards,

Hi,

This works great for extracting all the numbers, however it does pull some that start with numbers other than 3 or 4. How would I add a condition to only extract the digits that start with 3 or 4? Thanks!

 

[btwice]

MrExcel_2025-03.xlsm
	A	B	C	D	E	F
10	46353865					46353865
11	Line 137					null
12	31540572_46403933_31597936					31540572,46403933,31597936
13	35544223 and 35601630					35544223,35601630
003

Cell Formulas
Range	Formula
F10:F13	F10	= IFERROR(TEXTJOIN(",",TRUE,REGEXEXTRACT(A10, "(\d{8})",1,1)),"null")

Thanks for the reply, however I needed a power query solution here. This excel version of what I need does look interesting though, may have some use for it in other projects, thanks!

 

[alex78]

let
A =  Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
B = {"Column1", "Digits"},
C = Table.SplitColumn(A, B{0}, (x)=> {x, 
[ 
a = Table.FromColumns({{null} & Text.ToList(Text.From(x))},{"x"}), 
b = Text.Combine(List.Select(Table.Group(a, "x", {"y", each if List.Count([x])>=8 then Text.Select(Text.Combine([x]), {"0".."9"}) else null}
    ,0, (x,y)=> Byte.From(not List.ContainsAny({y},{"0".."9"})))[y], each List.ContainsAny({"3","4"}, {Text.Start(_,1)}) and Text.Length(_)=8), ",")
]
[b]}, B)
in C

 

[btwice]

let
A =  Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
B = {"Column1", "Digits"},
C = Table.SplitColumn(A, B{0}, (x)=> {x,
[
a = Table.FromColumns({{null} & Text.ToList(Text.From(x))},{"x"}),
b = Text.Combine(List.Select(Table.Group(a, "x", {"y", each if List.Count([x])>=8 then Text.Select(Text.Combine([x]), {"0".."9"}) else null}
    ,0, (x,y)=> Byte.From(not List.ContainsAny({y},{"0".."9"})))[y], each List.ContainsAny({"3","4"}, {Text.Start(_,1)}) and Text.Length(_)=8), ",")
]
[b]}, B)
in C

This is great, didn't think to just use contains and text start together, appreciate it!