Extract value from string following word from defined list - multiple times within one cell

[GK007]

Hello team,

Would really appreciate your help on this:

Example:
Cell contains the following (letters and figures, no particular size or order):
CODE-97 1340 : VARIETY P8039 BATCH Н-15-097/1465/2626709-931 BAGS; VARIETY P8521: BATCH Н-15-097/2030/2661313-166 BAGS

I need to extract varieties and corresponding number of bags into separate cells:
P8039 931 P8521 166

Another complication is that "VARIETY" can sometimes be "HYBRID" and a few other names, same for "BAGS" (can be "UNITS" and a few other words).
So challenge is creating a formula that will check values from the provided list (VARIETY, HYBRID, ABS, BVC, etc.) and then extract value following it within the cell on MULTIPLE occasions (because we can have several per cell).

Thanks a lot in advance!

 

[Fennek]

Hi,

it is a sunny Saturday and I like to joke a bit:

Try "RegEx" with
.Global = True
.Pattern = "VRIETY\s(\w\d+)|-(\d+)\sBAGS"

and read the SubMatches.

regards

(I suppose, if you could understand these comments, you don't need any help)

 

[GK007]

sorry, not clear, so still need help.

 

[Rick Rothstein]

Example:
Cell contains the following (letters and figures, no particular size or order):
CODE-97 1340 : VARIETY P8039 BATCH Н-15-097/1465/2626709-931 BAGS; VARIETY P8521: BATCH Н-15-097/2030/2661313-166 BAGS

I need to extract varieties and corresponding number of bags into separate cells:
P8039 931 P8521 166

If the "construction" of that single example is truly representative of what your data looks like, and assuming your data starts in cell A1, then this macro should do what you want...

[table="width: 500"]
[tr]
	[td]Sub VarietiesAndUnits()
  Dim R As Long, C As Long, X As Long
  Dim Data As Variant, Result As Variant, Parts() As String
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To Evaluate(Replace("MAX(LEN(@)-LEN(SUBSTITUTE(@,"" "","""")))", "@", Intersect(Columns("A"), ActiveSheet.UsedRange).Address)))
  For R = 1 To UBound(Data)
    C = 0
    Parts = Split(Data(R, 1))
    For X = 0 To UBound(Parts)
      If Parts(X) Like "[A-Z]####" Or Parts(X) Like "[A-Z]####:" Or _
         (Parts(X) Like "*/*#-#*" And Not Mid(Parts(X), InStrRev(Parts(X), "-") + 1) Like "*[!0-9]*") Then
        C = C + 1
        Result(R, C) = Replace(Mid(Parts(X), InStrRev(Parts(X), "-") + 1), ":", "")
      End If
    Next
  Next
  Range("B1").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub[/td]
[/tr]
[/table]

 

[GK007]

If the "construction" of that single example is truly representative of what your data looks like, and assuming your data starts in cell A1, then this macro should do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub VarietiesAndUnits()
  Dim R As Long, C As Long, X As Long
  Dim Data As Variant, Result As Variant, Parts() As String
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To Evaluate(Replace("MAX(LEN(@)-LEN(SUBSTITUTE(@,"" "","""")))", "@", Intersect(Columns("A"), ActiveSheet.UsedRange).Address)))
  For R = 1 To UBound(Data)
    C = 0
    Parts = Split(Data(R, 1))
    For X = 0 To UBound(Parts)
      If Parts(X) Like "[A-Z]####" Or Parts(X) Like "[A-Z]####:" Or _
         (Parts(X) Like "*/*#-#*" And Not Mid(Parts(X), InStrRev(Parts(X), "-") + 1) Like "*[!0-9]*") Then
        C = C + 1
        Result(R, C) = Replace(Mid(Parts(X), InStrRev(Parts(X), "-") + 1), ":", "")
      End If
    Next
  Next
  Range("B1").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

for some reason I get error Microsoft Visual Basic 400 when run the macro...anything I am doing wrong?

 

[hiker95]

GK007,

Welcome to the MrExcel forum.

Rick Rothstein's macro works for me.

Sample raw data, and results:

Excel 2007
	A	B	C	D	E
1	CODE-97 1340 : VARIETY P8039 BATCH ?-15-097/1465/2626709-931 BAGS; VARIETY P8521: BATCH ?-15-097/2030/2661313-166 BAGS	P8039	931	P8521	166
2	CODE-97 1340 : VARIETY P8044 BATCH ?-15-097/1465/2626709-944 BAGS; VARIETY P8544: BATCH ?-15-097/2030/2661313-144 BAGS	P8044	944	P8544	144
3
Sheet1

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

 

[GK007]

GK007,

Welcome to the MrExcel forum.

Rick Rothstein's macro works for me.

Sample raw data, and results:

Excel 2007

	A	B	C	D	E
1	CODE-97 1340 : VARIETY P8039 BATCH ?-15-097/1465/2626709-931 BAGS; VARIETY P8521: BATCH ?-15-097/2030/2661313-166 BAGS	P8039	931	P8521	166
2	CODE-97 1340 : VARIETY P8044 BATCH ?-15-097/1465/2626709-944 BAGS; VARIETY P8544: BATCH ?-15-097/2030/2661313-144 BAGS	P8044	944	P8544	144
3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Hi, I am on Mac latest version Excel. Thanks for your support!

 

[hiker95]

Hi, I am on Mac latest version Excel. Thanks for your support!

When asking for help in the future, you should add to your thread title, at least, On a Mac, and, then only those programmers that have experience with a Mac, will respond.

I have no experience with a Mac.

 

[Rick Rothstein]

Hi, I am on Mac latest version Excel. Thanks for your support!

Like hiker95, I also do not have experience with a Mac. I guess it is possible the problem you are having with my code is due to differences between your Mac and my PC. Another possibility is that your one example was not truly representative of all your data and the code might be reacting to a text construction that differs enough from what you posted to cause a problem. Like for hike95, the code I posted works for me (assuming your example was representative), so I am not sure what to tell you from my end.

 

[hiker95]

GK007,

I am not sure if you can, but, you could try going back to your reply #1, and, try to edit/add to the end of the title on a Mac

Or, maybe Rick Rothstein can suggest another way to do the above.