Excel: find/extract string with # characters (numbers) in cells-CodePudding

I have some large excel files from which I want to find numbers (SKU) with a certain length. Best case a cell looks like:

Itemname,itemname2 specs     1 234 1234567 K  3.000

Sometimes a cell contains more parts

Itemname,itemname2 specs     1 234 1234567 K  1.000  234 1234568 K  1.000  234 1234567 K  1.000  234 1234569 K  1.000  234 1234567 K  1.000

So it can contain the different and/or the same SKU multiple times in the same cell (1234567 is 3x, 1234568 1x and 1234569 1x)

And a few times it can look like this:

 Itemname,itemname2 specs     1 234 1234567*K  1.000  234 1234568 K  1.000

My goal is to extract all these 7-digits numbers (removing duplicates) and if possible also with "Itemname,itemname2 specs" in the next cell behind the 7-digit numbers

I did find some methods using "LEFT", "MID", "RIGHT", "SEARCH", "FIND", etc. but the examples I found don't work for my specific situation.

CodePudding user response：

Assuming your first entry is in A1, this formula in B1:

=LET(
    α, A1,
    ζ, SEQUENCE(, LEN(α) - 6),
    ξ, MMULT(SEQUENCE(, 7), N(ISERR(0   MID(MID(α, ζ, 7), SEQUENCE(7), 1)))) = 0,
    UNIQUE(0   MID(α, FILTER(ζ, ξ), 7), 1)
)

Copy down to give similar results for entries in A2, A3, etc.

CodePudding user response：

Try:

=LET(s,TEXTSPLIT(A1," "),
f, FILTER(s,(LEN(s)=7)*ISNUMBER(-s)),
UNIQUE(f,TRUE))

To extract the text, need more information and examples demonstrating the variability possibilities.