string containing both letter and numbers

[Mike]

I have a problem...
I need to find all resources with name that start with
the letter "K" and contains a number. The resources are
for instance K123, KA45, K888. But there are also
resources with names KXX where X is a letter.
How can I find all resources having names containing both
the letter K and one or more digits?

Thanks in advance,
Mike

 

[John]

Mike,
There are probably a couple different ways to do this. Here is one way.
First check the resource name string for the "K". If it will always be
the first character, use the "Mid" function to pull out the first
character. If the "K" may be "buried" in the string, use the "InStr"
function. After the "K" is found, set up a Case statement using the
InStr function to check for the presence of the digits 0 through 9, or 1
through 9 if 0 is not a valid value.

Once the applicable resource names have been found, either mark them
with a flag field, or load them into a matrix for further processing.

John

 

[JackD]

John wouldn't you use "left" to check if it was the first character?

ie:
If Left(mystring, 1) = "K" then...

 

[John]

Jack,
No actually I would still use "Mid", I guess because I'm so used to
using it. But if the "K" was the leftmost character, your suggestion
would work just as well.

John

 

[JackD]

Jack,
No actually I would still use "Mid", I guess because I'm so used to
using it. But if the "K" was the leftmost character, your suggestion
would work just as well.

John

I find I rarely use mid. But I'll give it a try. Sounds addictive.

-Jack

 

[Rod Gill]

Hi,

To be pedantic, especially if you can't guarantee that K is upper case it's
best to use:
=Ucase(Left(mystring, 1))="K"

--
Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/