So I have a list of parts that have been ordered for the maintenance crew at my job. There is almost zero uniformity across different records. Some are just a string of 6 numbers, others have letters, commas, periods, slashes, and hyphens and can get up to even like 10 characters long. Here are a couple examples:
PH30-001 S/S bolt/nut for visible mounting
120.0V AC Power Supply for Static-Eliminating Bar Part number: 2246K21
00118ET3E143TC-W2 AC MOTOR 1 H
G3083403, Pilot Light,LED,120V,30mm,Chrome,GR
REPAIR ALLEN BRADLEY VPF-B1652C-PJ12AF SERVO MOTOR
128.980.006, CONVERSION KIT FOR AXIAL FAN
CF-20TX-144 LEFT/RIGHT FOLDING ROD FOR T20CF
My job is essentially to extract the alphanumeric codes from these cells and put them in another column in excel. For instance:
PH30-001 S/S bolt/nut for visible mounting → PH30-001
120.0V AC Power Supply for Static-Eliminating Bar Part number: 2246K21 → 2246K21
00118ET3E143TC-W2 AC MOTOR 1 H → 00118ET3E143TC-W2
G3083403, Pilot Light,LED,120V,30mm,Chrome,GR → G3083403
REPAIR ALLEN BRADLEY VPF-B1652C-PJ12AF SERVO MOTOR → VPF-B1652C-PJ12AF
128.980.006, CONVERSION KIT FOR AXIAL FAN → 128.980.006
CF-20TX-144 LEFT/RIGHT FOLDING ROD FOR T20CF → CF-20TX-144
Is there anyway to do this without manually copy and pasting or typing thousands of records?
I used the text to columns tool, delimited by all options, and got most part numbers separated out, but the only way I can see moving forward from this option is to just copy and past thousands of separated part numbers back into the main sheet. Any help would be appreciated.
jjtank77 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Retrieve IDs From Column With No Uniformity
- It is debatable if there is no uniformity. You’ll have to prove it with some relevant sample data.
- I used the space and some punctuation characters to split the words in each cell.
- I used a minimum of 5 digits out of a minimum of 7 characters as the main conditions to retrieve the ID.
- I didn’t even bother to check if all characters are uppercase alpha-numeric, a period, or a dash. That’s probably not too hard to implement.
=LET(data,B2:B11,min_length,7,min_digits,5,if_not_found,NA(),
delimiters,{" ";",";":";";";"?";"!"},
Digits0,SEQUENCE(10)-1,
WordDigitsCount,LAMBDA(word,LET(
s,--MID(word,SEQUENCE(LEN(word)),1),
IFERROR(COUNT(TOCOL(XMATCH(s,Digits0),2)),))),
DigitWordsToCol,LAMBDA(word_col,min_digits,
DROP(REDUCE("",word_col,LAMBDA(rr,r,
IF(WordDigitsCount(r)>=min_digits,VSTACK(rr,r),rr))),1)),
r,BYROW(data,LAMBDA(r,LET(
s,TEXTSPLIT(r,,delimiters),
f,FILTER(s,LEN(s)>=min_length),
d,DigitWordsToCol(f,min_digits),
IFERROR(TAKE(d,1),if_not_found)))),
r)
The Lambdas (Name Manager)
Digits0: =SEQUENCE(10)-1
WordDigitsCount: =LAMBDA(word,LET(
s,--MID(word,SEQUENCE(LEN(word)),1),
IFERROR(COUNT(TOCOL(XMATCH(s,Digits0),2)),)))
DigitWordsToCol: =LAMBDA(word_col,min_digits,
DROP(REDUCE("",word_col,LAMBDA(rr,r,
IF(WordDigitsCount(r)>=min_digits,VSTACK(rr,r),rr))),1))
- If you’d create the Lambda functions above, the formula could look like this:
=LET(data,B2:B11,min_length,7,min_digits,5,if_not_found,NA(),
delimiters,{" ";",";":";";";"?";"!"},
BYROW(data,LAMBDA(r,LET(
s,TEXTSPLIT(r,,delimiters),
f,FILTER(s,LEN(s)>=min_length),
d,DigitWordsToCol(f,min_digits),
IFERROR(TAKE(d,1),if_not_found)))))
- Of course, you could easily convert it to another Lambda function.
1
Assuming your job can provide you with a master parts list, try the following in each part order row in column B:
=INDEX($C$53:$C$54,XMATCH("*"&C53&"*",$A$53:$A$55,2),1)
where column C is your parts list, column A is your parts orders.
3