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

How extract string of characters to the right of the 1st hyphen

If I have a string of characters that looks like this:

#10 Small - something - something else

And I want to extract in query designer this part of it: (everything to the right of the 1st hyphen not including the space in front of the 1st "something")

something - something else

How can I do this?
  • 2
2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Use a combination of Mid and Instr.

What have you tried so far?
SteveL13Author Commented:

But it isn't even close.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Mid([tblPaper.Description], InStr([tblPaper.Description], "-") + 2, Len([tblPaper.Description]) - InStr([tblPaper.Description], "-"))
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in case there may not be a space after the first hyphen, here is a slight modification to what Scott wrote to TRIM instead:
trim(Mid([tblPaper.Description], InStr([tblPaper.Description], "-") +1 ))

Open in new window

also, the 3rd argument, how many characters to get, is optional so I left it out

if you are doing this in a query, create another column to filter for only records having a dash:

field: Description
table: tblPaper
criteria: Like "*-*"

btw, Description is a reserved word

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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