[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

MS Access regex


I'm wondering how I can use VBA with I guess regex or some other function to get everything after "Music Lessons:" in these strings:
Music Lessons:Smith, John
Music Lessons:Alley, Joan
Music Lessons:Lee, Brady

Thank you,
Victor Kimura
Victor Kimura
3 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Right("Music Lessons:Lee, Brady", Len("Music Lessons:Lee, Brady") - InStr(1, "Music Lessons:Lee, Brady", ":"))

Edit the above to replace "Music Lessons:Lee, Brady" with whatever column / variable you are using for these names.
Patrick MatthewsCommented:
Another way:

Mid(SomeColumn, InStr(1, SomeColumn, "Music Lessons:") + 1)

If you can get away with just "whatever comes after the first colon":

Mid(SomeColumn, InStr(1, SomeColumn, ":") + 1)

To return a blank if "Music Lessons:" does not appear:

Mid(SomeColumn, InStr(1, SomeColumn & "Music Lessons:", "Music Lessons:") + 1)

To return a blank if no colon appears:

Mid(SomeColumn, InStr(1, SomeColumn & ":", ":") + 1)
käµfm³d 👽Commented:
...or even:

Dim onTheRight

onTheRight = Replace(theColumn, "Music Lessons:", "")

Open in new window


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now