Pull all 8 digit numbers from column

btwice

New Member
Joined
Dec 16, 2014
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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
 
Hi @btwice,

Power Query:
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,
 
Upvote 0

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