Parse cell using patterns

GregFullington

New Member
Joined
May 2, 2018
Messages
4
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Welcome to the MrExcel board!

Can you tell us in words what the various patterns are that you are searching for?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
AB
1KeyData
2Key1MEDICAL RECORDS** please review for approval .. pc 81406 81479x12
3Key2PC'S/FC A0433 Y5400 A0425 MEDICAL RECORDS: *92138
4Key3bncvn
5Key4
6Key5abc 02356/ X6767,87654.
Sheet1



Excel Workbook
AB
1KeyPattern
2Key181406
3Key181479
4Key2A0433
5Key2Y5400
6Key2A0425
7Key502356
8Key5X6767
9Key587654
10
Sheet8
 
Upvote 0
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".
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top