Parse cell using patterns

[GregFullington]

I have a series of patterns that I am searching for in a cell.
For each pattern I find in the cell, I want to parse the matching string and write it to a new row in a new worksheet, along with a key value in a separate cell.

I am writing a function in a module. I am able to use the like statement to determine if the pattern exists in the cell, but haven't found a solution to parse out the matching data. There will be multiple instances that I will want to extract.

If curCell.Value Like "*#####,*" Or c.Value Like "*#####.*" Or c.Value Like "*##### *" Or c.Value Like "*#####/*" Or c.Value Like "*#####x*" Or c.Value Like "*?####,*" Or c.Value Like "*?####.*" Or c.Value Like "*?#### *" Or c.Value Like "*?####/*" Then 'extract the data


Sample input data
A1 B1
Key1 MEDICAL RECORDS** please review for approval .. pc 81406 81479x12
Key2 PC'S/FC A0433 Y5400 A0425 MEDICAL RECORDS: *92138

Sample Output
A1 B1
Key1 81406
Key1 81479
Key2 A0433
Key2 Y5400
Key2 A0425

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you tell us in words what the various patterns are that you are searching for?

 

[GregFullington]

sure... The patterns are five numbers (0-9) followed by a period, four numbers followed by a comma, four numbers followed by a space, four numbers followed by a forward slash, four numbers followed by the alpha character x, a character (a-z) followed by 4 numbers and a period, a character followed by 4 numbers and a comma, a character followed by 4 numbers and a space, a character followed by 4 numbers and a forward slash, a character followed by 4 numbers and a the alpha character x.

 

[GregFullington]

sorry.. The patterns are five numbers (0-9) followed by a period, five numbers followed by a comma, five numbers followed by a space, five numbers followed by a forward slash, five numbers followed by the alpha character x, a character (a-z) followed by 4 numbers and a period, a character followed by 4 numbers and a comma, a character followed by 4 numbers and a space, a character followed by 4 numbers and a forward slash, a character followed by 4 numbers and a the alpha character x.

 

[Peter_SSs]

sorry.. The patterns are five numbers (0-9) followed by a period, five numbers followed by a comma, five numbers followed by a space, five numbers followed by a forward slash, five numbers followed by the alpha character x, a character (a-z) followed by 4 numbers and a period, a character followed by 4 numbers and a comma, a character followed by 4 numbers and a space, a character followed by 4 numbers and a forward slash, a character followed by 4 numbers and a the alpha character x.

OK, thanks. I think the following should do that for you but let me know if there are any failures or additional patterns.

I have assumed that the original data sheet is the active sheet when the code is run.

Test in a copy of your workbook.

Sub ParseValues()
  Dim RX As Object, M As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, k As Long
  Dim wsNew As Worksheet
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 2)
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "[\dA-Z]\d{4}[\., \/x]"
  For i = 1 To UBound(a)
    Set M = RX.Execute(a(i, 2))
    For Each itm In M
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = Left(itm, 5)
    Next itm
  Next i
  Set wsNew = Sheets.Add(After:=ActiveSheet)
  With wsNew.Columns("A:B")
    .NumberFormat = "@"
    .Rows(1).Value = Array("Key", "Pattern")
    .Rows(2).Resize(k).Value = b
    .AutoFit
  End With
End Sub

Here is my sample data and results:

Excel Workbook
	A	B
1	Key	Data
2	Key1	MEDICAL RECORDS** please review for approval .. pc 81406 81479x12
3	Key2	PC'S/FC A0433 Y5400 A0425 MEDICAL RECORDS: *92138
4	Key3	bncvn
5	Key4
6	Key5	abc 02356/ X6767,87654.
Sheet1

Excel Workbook
	A	B
1	Key	Pattern
2	Key1	81406
3	Key1	81479
4	Key2	A0433
5	Key2	Y5400
6	Key2	A0425
7	Key5	02356
8	Key5	X6767
9	Key5	87654
10
Sheet8

 

[GregFullington]

Thanks for the reply!
When executing, at the line of code:

.Rows(2).Resize(k).Value = b,

I receive an "Application-defined or object-defined error".

 

[Peter_SSs]

Thanks for the reply!
When executing, at the line of code:

.Rows(2).Resize(k).Value = b,

I receive an "Application-defined or object-defined error".

1. Any more information in that error message?

2. If you Debug at that point and hover over the variable k, what value is revealed?

3. What version of Excel and what operating system are you using?

4. Have you tested the code with only the sample data shown in this thread?

5. Can you confirm that there is not a comma at the end of that line of code, even though you have shown one there in your post?