Search String and Return Static Value (Like CASE + RegEX in SQL)

I'm trying to help out the wife with a task at work, and it's been a very long time since I've done any programming in either Excel or VB.

Essentially she has a column of values in varying formats, and, depending on which format they match, she needs to return a set value in another cell. In SQL this could easily be accomplished with a CASE statement combined with RegExLike, but I can't seem to figure out the equivalent for Excel.

Here are some examples of the possible input strings - where # represents a numeric, and A represents any alpha character. I think some of them have some special characters - like ( ) - but otherwise they follow these patterns.

##-##A
#-##A
#-##AAA
#-###A

And so on. The idea is to match the pattern and then return a corresponding value - since the input values can change, using a VLOOKUP doesn't really work, since you'd have to rebuild the lookup table every time, and by the time you're done you could have just done it by hand.

Any thoughts/suggestions?

Hi

Maybe a UDF?

Please Login or Register  to view this content.

If that doesn't do it, then put up an example file, showing the relevant string possibilities, and what it should return.

HTH

rylo

rylo