0

I'm struggling with regular expressions at present, and I have a specific problem I need to solve.

Where a named value after Chief Investigator exists (Here Mr A Smith), I want to extract Mr A Smith only. I know that in all cases, 'Mr A Smith' will be preceeded and followed by two astericks.

"**Chief Investigator:**Mr A Smith
**Sponsor**: xxxxxxxxxxx
**Study Team Contact**: xxxxxxxxx
**Grant deadline**: 
**Date EC meeting completed**: xxxxxxxxxx
**Upload Method**: xxxx

Any ideas gratefully recieved!

player0
  • 69,261
  • 8
  • 33
  • 67
Dave Papworth
  • 161
  • 1
  • 1
  • 7
  • 1
    Looks like you are looking to create a regex, but do not know where to get started. Please check [Reference - What does this regex mean](https://stackoverflow.com/questions/22937618) resource, it has plenty of hints. Also, refer to [Learning Regular Expressions](https://stackoverflow.com/questions/4736) post for some basic regex info. Once you get some expression ready and still have issues with the solution, please edit the question with the latest details and we'll be glad to help you fix the problem. – Wiktor Stribiżew Jun 25 '20 at 17:41
  • 1
    You might find the selected answer to [this SO question](https://stackoverflow.com/questions/53897464/regex-positive-lookbehind-in-google-sheets) helpful. – Cary Swoveland Jun 25 '20 at 18:16

1 Answers1

2

try:

=INDEX(TRIM(SPLIT(REGEXREPLACE(A1:A, ":\*\*|\*\*:", "♥"), "♥")),,2)

0

player0
  • 69,261
  • 8
  • 33
  • 67