Regular Expression Pattern question

paradox4o

New Member
Joined
Jul 30, 2019
Messages
3
Can anyone help with the following....
I am tryiing to write a Regular Expression Patterm to find all instances within a string.
The data is a date/time followed by a name followed by (Work notes) or (Shared notes) and then the actual notes.
e.g. the string would look like this....
26/07/2019 02:27:30 BST - System (Work notes)
Restore SLA reached 50%​
24/07/2019 09:12:34 BST - Mark Shaw (Shared notes)
Will be back at her computer at about 14:00 today.​
22/07/2019 12:55:29 BST - Mark Shaw (Work notes)
Requested a call back at 13:30.​
22/07/2019 12:13:58 BST -
Mark O'Brien
(Work notes)
'Mark Shaw' has called 'Sandra Roden'​

So I would like the pattern to find the following
Match 1 26/07/2019 02:27:30 BST - System (Work notes)
Match 2 24/07/2019 09:12:34 BST - Mark Shaw (Shared notes)
Match 3 22/07/2019 12:55:29 BST - Mark Shaw (Work notes)
Match 4 22/07/2019 12:13:58 BST - Mark O'Brien (Work notes)

I don't seem to have a problem matching the date/time but can't seem to expand the pattern match to find the rest of the line? (reason I want to do this rather than just use the date/time is sometimes date/times are also found in the notes and I wish to ignore these)
Any suggestions would be greatly appreciated.
 
Last edited by a moderator:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Welcome to the MrExcel board!

Is that sample data
- All in one cell with Char(10) characters forcing the new lines, or
- In 10 different cells down a column, or
- Something else?
 
Last edited:
Upvote 0
Hi Peter_SSs thanks for the quick response
Yes that is a sample of the data.

What I have is one cell with a mass of text containing the data I showed you.
I am running a macro that is grabbing the contents of the cell as a single string (this string may contain 10 or more work notes), then running a gen exp against this string to find a number of matches.

I am then on a new sheet pasting each match as a new row.

First I did it by just matching the date and time but found that sometimes the note itself had included dates and times and so the macro failed. I wished to make the gen exp more explicit.
So the pattern should always start with a date time followed by the string "BST - " followed by a name which could be any length and could contain a"-" or " ' " character for hyphenated names like O'Brien or double barreled surnames and then a text string of either (Work notes) or (Shared notes) and a carriage return (or new line).

Mike
 
Upvote 0
See if this helps.
With your regular expression, ensure Global = True and IgnoreCase = True
Use this Pattern
"(\d{1,2}\/){2}\d{4} (\d{2}:){2}\d{2} BST \- .*?\((Work|Shared) notes\)"
then use something like this
.Execute(Replace(Range("A1").Value, vbLf, " "))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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