Remove Certain Last Words from String

I'm attempting to extract the Street Names from an Address.

For Example, if given "123 Very Big Street" I'd want to extract "Very Big".

If given, "123 Very Big" I'd want the same answer.

I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".

How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.

Thanks,

Shred

Please Login or Register  to view this content.

What I was getting at is having about 50 or so strings to match against.

Street, Drive, Way, Lane, road, rd., st., Blvd., Avenue, Ave, etc.

Thanks for the reply.

I'm trying to keep the maintenance of the list of lastwords out of the user's hands. I want to keep it in the code. Either way, I have to type the values somewhere.

I was thinking I could somehow populate an array with the values, and then loop through them to see if I have a match with the curent string.

I guess I could hide the worksheet the dynamic range list is on.

Any other ideas?

Thanks,

Shred

Thanks Mikerickson. That's the kind of thing I was envisioning. I had made similar attempts but never quite got my syntax correct I guess.

Thanks for pointing out that MATCH is is not case sensitive. It looks like this will do the trick for me.

Shred

Wasn't unhappy...just didn't click with me. Maybe it was way over my head.

Thanks for replying.

Thanks Mikerickson for pointing me in this direction. I adapted your ideas into some code that eventually worked. I was unable to get the results I desired using ISNUMERIC when there was not a match as an error would be returned by the Match worksheet function; that worked fine if is there was a match between the lastword and the list of choices. So I resorted to ISERROR instead and got the results I needed. It now works whether there is a match or not.

I also got it working with a dynamic range pulled from the workbook ('commented line)

See code below.

Thanks again.

Please Login or Register  to view this content.

Correct. I was testing both methods. I prefer the array and maintaining the list in the code for this particular application.