regular expression syntax

[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

[Niki Estner]

^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

"Tim Marsden" <[e-mail]> wrote in
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Niki Estner]

^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

"Tim Marsden" <[e-mail]> wrote in
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Tim Marsden]

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" <ni*********@cube.net> wrote in message
news:OW**************@TK2MSFTNGP12.phx.gbl...

^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

"Tim Marsden" <[e-mail]> wrote in
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Tim Marsden]

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" <ni*********@cube.net> wrote in message
news:OW**************@TK2MSFTNGP12.phx.gbl...

^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

"Tim Marsden" <[e-mail]> wrote in
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Brian Davis]

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" <[e-mail]> wrote in message
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Brian Davis]

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" <[e-mail]> wrote in message
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Niki Estner]

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" <[e-mail]> wrote in
news:eZ**************@tk2msftngp13.phx.gbl...

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" <ni*********@cube.net> wrote in message
news:OW**************@TK2MSFTNGP12.phx.gbl...

^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

"Tim Marsden" <[e-mail]> wrote in
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Niki Estner]

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" <[e-mail]> wrote in
news:eZ**************@tk2msftngp13.phx.gbl...

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" <ni*********@cube.net> wrote in message
news:OW**************@TK2MSFTNGP12.phx.gbl...

^([a-zA-Z]+)(\d+)$

Note: this will not accept R1C1-references!

Niki

"Tim Marsden" <[e-mail]> wrote in
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Tian Min Huang]

Hi Tim,

Thanks for your post. In addition to the replied from other community
members, I also recommend you the following web site:
http://regexlib.com/Default.aspx

Have a nice day!

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.

[Tian Min Huang]

Hi Tim,

Thanks for your post. In addition to the replied from other community
members, I also recommend you the following web site:
http://regexlib.com/Default.aspx

Have a nice day!

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.

[Tim Marsden]

Many Thanks for all suggests, will test and post results

"Brian Davis" <br***@knowdotnet.nospam.com> wrote in message
news:Ob*************@tk2msftngp13.phx.gbl...

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" <[e-mail]> wrote in message
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Tim Marsden]

Many Thanks for all suggests, will test and post results

"Brian Davis" <br***@knowdotnet.nospam.com> wrote in message
news:Ob*************@tk2msftngp13.phx.gbl...

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" <[e-mail]> wrote in message
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Tian Min Huang]

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.

[Tim Marsden]

Thanks,

The suggested expression works as expected.
"Brian Davis" <br***@knowdotnet.nospam.com> wrote in message
news:Ob*************@tk2msftngp13.phx.gbl...

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" <[e-mail]> wrote in message
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

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

Thank
Tim

[Backslider]

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" <[e-mail]> wrote in message news:<#Z**************@TK2MSFTNGP11.phx.gbl>...

Thanks,

The suggested expression works as expected.
"Brian Davis" <br***@knowdotnet.nospam.com> wrote in message
news:Ob*************@tk2msftngp13.phx.gbl...

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.

[Tim Marsden]

Thanks

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

Tim

"Backslider" <ba********@mail.com> wrote in message
news:c3**************************@posting.google.c om...

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" <[e-mail]> wrote in message

news:<#Z**************@TK2MSFTNGP11.phx.gbl>...

Thanks,

The suggested expression works as expected.
"Brian Davis" <br***@knowdotnet.nospam.com> wrote in message
news:Ob*************@tk2msftngp13.phx.gbl...

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.