Extracting particular data in substring but location is never the same place

[kgarland17]

I have tens of thousands of SKUs and I need to be able to pull a particular attribute from them all into one column but the attribute is never in the same location across all the SKUs.
How does/can one pull a 1-3 digit number w/ the same letter after it?

example: need to extract the bold data

REST-B-AQ-3FD-6Z-32V-DE-XX VRZA-B-16Z-29SR-1G-HR-XS-XX DELI-B-XP-F5S-5W-22-BW-155Z-XX

 

[Micron]

3 examples out of 10,000 doesn't provide a lot of info to work with. You'd have to identify a reliable logical pattern. From what I can see, the approach might be to use vba to:
- loop over each character in the string and find a z.
- if the preceding character is a number (16Z), find the preceding "-". Grab everything between the - and the z.
- if preceding character is not a number (VRZA), find the next z. Rinse and repeat.

If there can never be a z after the z that is preceded with a number then it might be easier to start a search from the right instead of the left.

 

[shaowu459]

according to the sample data, please try below formula:

Book2
	A	B	C
1	REST-B-AQ-3FD-6Z-32V-DE-XX	6Z
2	VRZA-B-16Z-29SR-1G-HR-XS-XX	16Z
3	DELI-B-XP-F5S-5W-22-BW-155Z-XX	155Z
4
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(s,TEXTSPLIT(A1,"-"),FILTER(s,(RIGHT(s)="z")*ISNUMBER(-LEFT(s,LEN(s)-1))))

 

[Rick Rothstein]

A better set of sample data would have been helpful. However, based on what you posted, this single formula should process your entire range (just change the range reference to your actual data)...

=TEXTAFTER(TEXTBEFORE(A1:A3&"-","Z-",-1),"-",-1)&"Z"

 

[Rick Rothstein]

A better set of sample data would have been helpful. However, based on what you posted, this single formula should process your entire range (just change the range reference to your actual data)...

=TEXTAFTER(TEXTBEFORE(A1:A3&"-","Z-",-1),"-",-1)&"Z"

And if it is possible that a cell might not have a number followed by a "Z" in it, then use this formula instead...

=LET(c,TEXTAFTER(TEXTBEFORE(A1:A3&"-","Z-",-1),"-",-1),IF(ISNUMBER(-c),c&"Z",""))

 

[Peter_SSs]

Without knowing what the actual data may contain, I can see some possible issues with both post 3 & 5 formulas - see first mini-sheet below.

kgarland.xlsm
	A	B	C	D	E	F	G
1	REST-B-25Z-2345Z-AQ-3FD-2DECZ-32V	25Z	2345Z	2DECZ			2DECZ
2	VRZA-B-16Z-29SR-1G-HR-XS-XX	16Z					16Z
3	DELI-B-XP-F5S-5W-22-BW-155Z-XX	155Z					155Z
4	ABC-45Z-123456Z	45Z	123456Z				123456Z
5	A-B-C	#CALC!
Z

Cell Formulas
Range	Formula
G1:G5	G1	=LET(c,TEXTAFTER(TEXTBEFORE(A1:A5&"-","Z-",-1),"-",-1),IF(ISNUMBER(-c),c&"Z",""))
B1:D1,B4:C4,B2:B3,B5	B1	=LET(s,TEXTSPLIT(A1,"-"),FILTER(s,(RIGHT(s)="z")*ISNUMBER(-LEFT(s,LEN(s)-1))))
Dynamic array formulas.

Below is a more long-winded worksheet formula (perhaps it can be shortened?) that covers at least some more possible cases and also a user-defined function that might be considered.

Function GetZ(s As String, Optional MaxPrefixDigits As Long = 3) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^|-)\d{1," & MaxPrefixDigits & "}Z(?=-|$)"
  If RX.Test(s) Then GetZ = Mid(RX.Execute(s)(0), 2)
End Function

kgarland.xlsm
	A	B	C
1	REST-B-25Z-2345Z-AQ-3FD-2DECZ-32V	25Z	25Z
2	VRZA-B-16Z-29SR-1G-HR-XS-XX	16Z	16Z
3	DELI-B-XP-F5S-5W-22-BW-155Z-XX	155Z	155Z
4	ABC-45Z-123456Z	45Z	45Z
5	A-B-C
Z (2)

Cell Formulas
Range	Formula
B1:B5	B1	=LET(c,MID(A1,SEQUENCE(LEN(A1)),1),s,CONCAT("-",IF(ISNUMBER(-c),1,c),"-"),p,MIN(IFERROR(FIND({"-1Z-","-11Z-","-111Z-"},s),999)),IFERROR(REPLACE(LEFT(A1,FIND("-",s,p+1)-2),1,p-1,""),""))
C1:C5	C1	=GetZ(A1)

 

[kgarland17]

Thank you all for the helpful input!!

 

[Peter_SSs]

You're welcome. Hope that you can find something that suits your purpose among the several offerings.

As mentioned previously, I don't know how varied your data might be but if the 'Z attribute' that you are trying to find could be the first thing in the string, then my suggested user-defined function would return an incorrect result ("6Z' for the last example below). Here is a correction to the UDF to allow for that circumstance.

Function GetZ(s As String, Optional MaxPrefixDigits As Long = 3) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "-\d{1," & MaxPrefixDigits & "}Z(?=-|$)"
  If RX.Test("-" & s) Then GetZ = Mid(RX.Execute("-" & s)(0), 2)
End Function

kgarland.xlsm
	A		C
1	REST-B-25Z-2345Z-AQ-3FD-2DECZ-32V		25Z
2	VRZA-B-16Z-29SR-1G-HR-XS-XX		16Z
3	DELI-B-XP-F5S-5W-22-BW-155Z-XX		155Z
4	ABC-45Z-123456Z		45Z
5	A-B-C
6	26Z-3R-B-123G		26Z
Z (2)

Cell Formulas
Range	Formula
C1:C6	C1	=GetZ(A1)

 

[shaowu459]

Extract string that ending with “z”, 1~3 digital numbers before "z", return the first matching item and return "" is there is no mathing items:

Book1.xlsx
	A	B	C
1	REST-B-25Z-2345Z-AQ-3FD-2DECZ-32V	25Z
2	VRZA-B-16Z-29SR-1G-HR-XS-XX	16Z
3	DELI-B-XP-F5S-5W-22-BW-155Z-XX	155Z
4	ABC-45Z-123456Z	45Z
5	A-B-C
6	26Z-3R-B-123G	26Z
7	2612Z-3R-B-123G
8	3R-B-123G-Z
9	3R-B-124G-1Z	1Z
10	3R-1234z-124G-23Z	23Z
11
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=LET(s,TEXTSPLIT(A1,"-"),@FILTER(s,(RIGHT(s)="z")*ISNUMBER(VDB(,,999,0,--LEFT(s,LEN(s)-1))),""))

 

[Peter_SSs]

Extract string that ending with “z”, 1~3 digital numbers before "z", return the first matching item and return "" is there is no mathing items:

Interesting formula! I have not seen VDB() used before. Could it be tweaked in case these are possible?

kgarland.xlsm
	A	B
10	B-000891Z-36Z	000891Z
11	B6-1E1Z-123Z	1E1Z
Sheet1

Cell Formulas
Range	Formula
B10:B11	B10	=LET(s,TEXTSPLIT(A10,"-"),@FILTER(s,(RIGHT(s)="z")*ISNUMBER(VDB(,,999,0,--LEFT(s,LEN(s)-1))),""))