Macro to count characters in a cell

[chromachem]

We have run into a y2k10 problem.
Our lab uses lab id sample numbering scheme that changes from decade to decade.
At the end of 2009 a labid would have had 6 digits.
In the new decade we have started back at 4 digits.

In old labid might have been: 912001 6 digits
In this decade the id might be 1001 4 digits

when we run a duplicate, an old would be identified as 912001D 7 characters
In the new decade the id would be 1001D 5 characters

One of our vb programs uses the following code:

Please Login or Register  to view this content.

In the new decade, our labid's have gone from a 6 digit id (912001 for ex) to a 4 digit ID (10001 for ex).

For the moment I changed Left(strLabID, 6) to Left(strLabID, 4).

In the 10th month our labids will be 5 digits, and from 2011 - 2019 6 digits.

D, SP and R are added to the labid to signify Duplicate, spike and rerun.

I would like to future proof the code so that if I am not around on certain dates, the code will still work.

I need to be able to count the number of characters prior to d, sp and r so that I can create a new variable (newvar).
The final code would be Left(strLabID, newvar-1) for D, R
and Left(strLabID, newvar-2) for SP

In brief: I need the code to ell me how many characters are in a cell and be able to use that info in a variable.

Thanks

[DonkeyOte]

If you want the numeric value - given the numerics precede the Alphas why not just:

Please Login or Register  to view this content.

[chromachem]

Why not indeed. Nothing better than a simple and straight forward solution. I was not familiar with what the val expression does.

Worked like a charm.

Thank You so much!