Check if character is a letter or a number

Hey all,

I have a cell range that is passed as a String to a function, and within that function I need to extract only the Column letter. If it was just 1 letter it would be simple, but it may be 2, so does anybody know of a way of testing to see if the second character is a letter or a number?

Thanks.

THis may help a little:

Please Login or Register  to view this content.

then just use the function Isnumber in the code if it returns true it's a number if its's false it's a string?

How are you constructing the range reference in the very first instance... ie show us the construct of the function from the native cell ?

In general you can use IsNumeric in VBA but I would say this is going the long way around (remember in 2007 the column identifier could be 3 chars long) ... IMO you'd be better off passing either the range as an actual range to the Function or:

Please Login or Register  to view this content.

Out of idle curiosity - why do you need the letter in the first instance ?

It's ok, I've just had a moment! I am constructing the Range from a Letter and a Number, so I'm just gonna pass them seperatly to the function and put them together in the one instance to make the range, the rest of the time I only need the Column anyway.

Thanks.

In VBA you're generally far better off using R1C1 type notation in code as opposed to A1 notation, eg for B10:

Cells(10,2)

as opposed to

Range("B10")

... the reasons are as you've just illustrated quite plain to see ie much easier to increment from column 26 to column 27 than from Z to AA etc...

Fair point, it is after all alot easier to work with numbers rather than letters. I find it odd that it is the other way around though - it would seem to make more sense if "B10" was equal to (2,10) rather than (10,2), but hey-ho, I'm no expert!