regular expression syntax

  • Thread starter Thread starter Tim Marsden
  • Start date Start date
T

Tim Marsden

Hi,

Can you tell me the regular expression syntax needed to validate an Excel
Cell Reference.
e.g. A1, B1200, IV54000 etc

Thank
Tim
 
Thanks for the reply,
This expression also acepts invalid entries e.g. QQ1, F99999 etc.
I only want to accept A1 throught to IV65535, valid cell referneces.
I am a complete expression novice, so don't understand why.

Thanks
Tim

Niki Estner said:
^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

Tim Marsden said:
Hi,

Can you tell me the regular expression syntax needed to validate an Excel
Cell Reference.
e.g. A1, B1200, IV54000 etc

Thank
Tim
Click to expand...
Click to expand...
 
I believe this expression will do it, but I haven't thoroughly tested it:

^([A-Z]|[A-I][A-V])(?>0*)(6553[0-6]|655[0-2]\d{1}|65[0-4]\d{2}|6[0-4]\d{3}|[
1-5]\d{4}|\d{2,4}|[1-9])$

You can use the Ignore Case option if you want to match capital or lower
case letters.
This should match A1 - IV65536.

Brian Davis
http://www.knowdotnet.com
 
This is possible, look for IP-address-validation examples on the net to get
the idea.
However, these range-check-regexes tend to get unreadable: if you will
convert the reference to row/column indices afterwards anyway, I'd suggest
doing the range check on these integers instead of inside the regex - this
will be more readable in most cases and more efficient in all cases. Integer
comparison operators are damn efficient and quite readable.

Niki

Tim Marsden said:
Thanks for the reply,
This expression also acepts invalid entries e.g. QQ1, F99999 etc.
I only want to accept A1 throught to IV65535, valid cell referneces.
I am a complete expression novice, so don't understand why.

Thanks
Tim

Niki Estner said:
^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

Tim Marsden said:
Hi,

Can you tell me the regular expression syntax needed to validate an Excel
Cell Reference.
e.g. A1, B1200, IV54000 etc

Thank
Tim
Click to expand...
Click to expand...
Click to expand...
 
Many Thanks for all suggests, will test and post results

Brian Davis said:
I believe this expression will do it, but I haven't thoroughly tested it:

^([A-Z]|[A-I][A-V])(?>0*)(6553[0-6]|655[0-2]\d{1}|65[0-4]\d{2}|6[0-4]\d{3}|[
1-5]\d{4}|\d{2,4}|[1-9])$

You can use the Ignore Case option if you want to match capital or lower
case letters.
This should match A1 - IV65536.

Brian Davis
http://www.knowdotnet.com



Tim Marsden said:
Hi,

Can you tell me the regular expression syntax needed to validate an Excel
Cell Reference.
e.g. A1, B1200, IV54000 etc

Thank
Tim
Click to expand...
Click to expand...
 
Hi Tim,

Do you have any update that you'd like to share with us?

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Thanks,

The suggested expression works as expected.


Brian Davis said:
I believe this expression will do it, but I haven't thoroughly tested it:

^([A-Z]|[A-I][A-V])(?>0*)(6553[0-6]|655[0-2]\d{1}|65[0-4]\d{2}|6[0-4]\d{3}|[
1-5]\d{4}|\d{2,4}|[1-9])$

You can use the Ignore Case option if you want to match capital or lower
case letters.
This should match A1 - IV65536.

Brian Davis
http://www.knowdotnet.com



Tim Marsden said:
Hi,

Can you tell me the regular expression syntax needed to validate an Excel
Cell Reference.
e.g. A1, B1200, IV54000 etc

Thank
Tim
Click to expand...
Click to expand...
 
Are you sure? Just from looking at it.. doesn't that say the letter
part has to either be a single letter A-Z or A-I followed by A-V? So
doesn't that mean AX or AY would not match? It seems like you'd
need... A-Z or A-H+A-Z or I+A-V..

What about the possible $'s? Like $AY$345?

thanks,
Backslider

Tim Marsden said:
Thanks,

The suggested expression works as expected.


Brian Davis said:
I believe this expression will do it, but I haven't thoroughly tested it:

^([A-Z]|[A-I][A-V])(?>0*)(6553[0-6]|655[0-2]\d{1}|65[0-4]\d{2}|6[0-4]\d{3}|[
1-5]\d{4}|\d{2,4}|[1-9])$

You can use the Ignore Case option if you want to match capital or lower
case letters.
This should match A1 - IV65536.
Click to expand...
Click to expand...
 
Thanks

I think you are right. Can you suggest the correct expression?

Tim

Backslider said:
Are you sure? Just from looking at it.. doesn't that say the letter
part has to either be a single letter A-Z or A-I followed by A-V? So
doesn't that mean AX or AY would not match? It seems like you'd
need... A-Z or A-H+A-Z or I+A-V..

What about the possible $'s? Like $AY$345?

thanks,
Backslider

"Tim Marsden" <[email protected]> wrote in message
Thanks,

The suggested expression works as expected.


Brian Davis said:
I believe this expression will do it, but I haven't thoroughly tested it:
Click to expand...
^([A-Z]|[A-I][A-V])(?>0*)(6553[0-6]|655[0-2]\d{1}|65[0-4]\d{2}|6[0-4]\d{3}|[
1-5]\d{4}|\d{2,4}|[1-9])$

You can use the Ignore Case option if you want to match capital or lower
case letters.
This should match A1 - IV65536.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top