String Parse

[Chris]

The company I work for has a customer table and in the customer name filed
they have the customer name along with the store number. Example: "central
pipe #123" or "central pipe #12345". I would like to grab just the store
number so I need to know how to search the string for the number sign and
grab everything that follows it. Can anyone help me?

thanks,
Chris

 

[Guest]

Hi Chris--My original post was meant for another poster so ignor it...thanks.

Use a combination of the InStr and Mid functions to get what you need.

Mid([Store Number], InStr([Store Number], "#"))

Give it a try and post back on the success/failure.

 

[Dirk Goldgar]

Hi Chris--My original post was meant for another poster so ignor
it...thanks.

Use a combination of the InStr and Mid functions to get what you need.

Mid([Store Number], InStr([Store Number], "#"))

Give it a try and post back on the success/failure.

If you want to drop the "#" from the front, I think you'll need to add 1
to the return value from InStr:

Mid([Store Number], 1 + InStr([Store Number], "#"))

There'll be a problem if there's no store number (and no "#") in the
field, though. If you think that could happen, you could use an IIf()
condition to check for it, or write a function that does the checking
and returns the store number if it's present.