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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,169
Office Version
365
Platform
Windows
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:

paradox4o

New Member
Joined
Jul 30, 2019
Messages
3
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,169
Office Version
365
Platform
Windows
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:

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top