Regular Expression for cell address

[M. Authement]

Can someone explain this regular expression to me? I found it in some VBA
code for finding/altering a cell address within a string. I put spaces in
to break the expression apart into the parts (I think) I understand.

(?:[\^\])-/+*:,="[(]) (\$?) ([A-Z]{1,2}) (\$?) (\d{1,5}) ([^\d]|$)

The second and fourth part are the optional absolute/relative dollar sign,
the third part is the one or two character column, and the fifth part is the
1 to 5 digit row number.

What are the first and last parts of this expression?

Is there a more robust way to designate the row number as 1-65536?

What about the column designation? I was thinking something like
([A-Za-z]|[A-Za-z][A-Za-z]|[Ii][A-Va-v]). This allows for lower case
letters and avoids the possibility of columns greater than IV.

I am new to regular expressions, having read an online tutorial, so any
references to sources of help are also appreciated. Thanks in advance for
your help!

 

[Scott]

I'd actually have to read up on VBA Regex, so I'll leave the
explanation to someone with a working understanding.

Your second part... that is not going to work like you want it to.

([A-Za-z]|[A-Za-z][A-Za-z]|[Ii][A-Va-v]) allows ZZ

You probably meant:
([A-Za-z]|[A-Ha-h][A-Za-z]|[Ii][A-Va-v])

Scott

 

[Dana DeLouis]

(?:[\^\])-/+*:,="[(])

Hi believe brackets capture any of the items within [ ??? ].
In general, (?: __ ) is a subexpression that matches pattern, but does not
capture the match for use later on. (ie looking at a formula =Sum(..) )

I'm not to sure of the last on. ([^\d]|$)
$ usually matches the position at the end of the string.
| is "Or"
[^\d] is a non-digit, but could have been written as \D.
Again, not too sure on that one. ??

 

[Ron Rosenfeld]

Can someone explain this regular expression to me? I found it in some VBA
code for finding/altering a cell address within a string. I put spaces in
to break the expression apart into the parts (I think) I understand.

(?:[\^\])-/+*:,="[(]) (\$?) ([A-Z]{1,2}) (\$?) (\d{1,5}) ([^\d]|$)

The second and fourth part are the optional absolute/relative dollar sign,
the third part is the one or two character column, and the fifth part is the
1 to 5 digit row number.

What are the first and last parts of this expression?

Is there a more robust way to designate the row number as 1-65536?

What about the column designation? I was thinking something like
([A-Za-z]|[A-Za-z][A-Za-z]|[Ii][A-Va-v]). This allows for lower case
letters and avoids the possibility of columns greater than IV.

I am new to regular expressions, having read an online tutorial, so any
references to sources of help are also appreciated. Thanks in advance for
your help!

The last part is relatively easy.

It says match anything that is either
NOT a digit [^\d]
or
is the end of line.

I think equivalent expressions would be:

(\D|$)

([\D$])

My guess is that the last would be the most efficient.

The first expression is more complex.

(?attern) is a non-capturing match.

In this case you are looking to match, but not capture, any single character
within the opening and closing brackets. The opening bracket is just after the
initial colon ":", and the closing bracket is just before the ending close
parenthesis.

The carat (^) and right bracket (]) are preceded by the forward slash (\) so
they will be interpreted as literals within the bracket expression. All of the
other characters within the bracket expression should be interpreted as
literals, if I recall correctly.


--ron

 

[M. Authement]

Thanks Scott. Yes, you are right, I intended the second set to be A-H, not
A-Z.

I'd actually have to read up on VBA Regex, so I'll leave the
explanation to someone with a working understanding.

Your second part... that is not going to work like you want it to.

([A-Za-z]|[A-Za-z][A-Za-z]|[Ii][A-Va-v]) allows ZZ

You probably meant:
([A-Za-z]|[A-Ha-h][A-Za-z]|[Ii][A-Va-v])

Scott

Can someone explain this regular expression to me? I found it in some
VBA
code for finding/altering a cell address within a string. I put spaces
in
to break the expression apart into the parts (I think) I understand.

(?:[\^\])-/+*:,="[(]) (\$?) ([A-Z]{1,2}) (\$?) (\d{1,5}) ([^\d]|$)

The second and fourth part are the optional absolute/relative dollar
sign,
the third part is the one or two character column, and the fifth part is
the
1 to 5 digit row number.

What are the first and last parts of this expression?

Is there a more robust way to designate the row number as 1-65536?

What about the column designation? I was thinking something like
([A-Za-z]|[A-Za-z][A-Za-z]|[Ii][A-Va-v]). This allows for lower case
letters and avoids the possibility of columns greater than IV.

I am new to regular expressions, having read an online tutorial, so any
references to sources of help are also appreciated. Thanks in advance
for
your help!

 

[M. Authement]

Thanks Ron.

Does that mean that the potential cell address MUST be preceeded by one of
the characters in the first part of the expression in order for the rest of
the expression to be true (found in a string)?

Also, any thoughts on making the row number expression more robust?

Can someone explain this regular expression to me? I found it in some VBA
code for finding/altering a cell address within a string. I put spaces in
to break the expression apart into the parts (I think) I understand.

(?:[\^\])-/+*:,="[(]) (\$?) ([A-Z]{1,2}) (\$?) (\d{1,5}) ([^\d]|$)

The second and fourth part are the optional absolute/relative dollar sign,
the third part is the one or two character column, and the fifth part is
the
1 to 5 digit row number.

What are the first and last parts of this expression?

Is there a more robust way to designate the row number as 1-65536?

What about the column designation? I was thinking something like
([A-Za-z]|[A-Za-z][A-Za-z]|[Ii][A-Va-v]). This allows for lower case
letters and avoids the possibility of columns greater than IV.

I am new to regular expressions, having read an online tutorial, so any
references to sources of help are also appreciated. Thanks in advance for
your help!

The last part is relatively easy.

It says match anything that is either
NOT a digit [^\d]
or
is the end of line.

I think equivalent expressions would be:

(\D|$)

([\D$])

My guess is that the last would be the most efficient.

The first expression is more complex.

(?attern) is a non-capturing match.

In this case you are looking to match, but not capture, any single
character
within the opening and closing brackets. The opening bracket is just
after the
initial colon ":", and the closing bracket is just before the ending close
parenthesis.

The carat (^) and right bracket (]) are preceded by the forward slash (\)
so
they will be interpreted as literals within the bracket expression. All
of the
other characters within the bracket expression should be interpreted as
literals, if I recall correctly.


--ron

 

[Harlan Grove]

M. Authement wrote...

Can someone explain this regular expression to me? I found it in some VBA
code for finding/altering a cell address within a string. I put spaces in
to break the expression apart into the parts (I think) I understand.

(?:[\^\])-/+*:,="[(]) (\$?) ([A-Z]{1,2}) (\$?) (\d{1,5}) ([^\d]|$)

....

The first, (?:...) looks like it's supposed to be any character that
could precede a valid cell address, but it doesn't include !, so this
regex won't match cell addresses that include the worksheet name. Also
it's got an innocuous bug: if hyphen, -, should be included in the
class, it should be the first character in the class. If the original
author intended it to be a character range from ) to /, then that would
have included * + , - . as well as ) and /, so the separate appearance
of those other characters is unnecessary.

The next bit matches zero or one literal dollar sign.

The next one or two letters.

The next another zero or one literal dollar sign.

The next one to five decimal numerals.

The last matches anything other than a decimal numeral or end-of-line,
but it's inclusive.

Much more efficient to use (remove the spaces)

\b (\$?) ([A-Ha-h]?[A-Za-z]|[Ii][A-Va-v]) (\$?) (\d{1,5}) \b

There's no need to parenthesize the break patterns, \b. Technically
this would match tokens like AX99999, which is NOT a valid cell address
but IS a valid name. Restricting the row number token to 1..65536 would
require something like

([1-5]?\d{1,4}|6([0-4]\d{3}|5([0-4]\d{2}|5([0-2]\d|3[0-6]))))

if you want to be a real stickler.

 

[Ron Rosenfeld]

Thanks Ron.

Does that mean that the potential cell address MUST be preceeded by one of
the characters in the first part of the expression in order for the rest of
the expression to be true (found in a string)?
Yes



Also, any thoughts on making the row number expression more robust?

Depending on how you are implementing this, it might be simpler to capture the
digit string and then test it to see if it is in the range.

You can do it with a regex, but the one I came up with is messy:

\b(([1-9]\d{0,3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d{2})|(655[0-2]\d)|(6553[0-6]))\b


Maybe someone has a simpler one.

Oh, you can combine some of the stuff at the end (the 65's), but you'd still
have just as much alternation and it would be more difficult to understand.

Also, you'd need to change the starting word boundary token (\b) to represent
your column letters optionally followed by the "$".


--ron

 

[M. Authement]

Thanks Ron. Makes sense to test the digits separately. I'll give it a try.

Thanks Ron.

Does that mean that the potential cell address MUST be preceeded by one of
the characters in the first part of the expression in order for the rest
of
the expression to be true (found in a string)?
Yes



Also, any thoughts on making the row number expression more robust?

Depending on how you are implementing this, it might be simpler to capture
the
digit string and then test it to see if it is in the range.

You can do it with a regex, but the one I came up with is messy:

\b(([1-9]\d{0,3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d{2})|(655[0-2]\d)|(6553[0-6]))\b


Maybe someone has a simpler one.

Oh, you can combine some of the stuff at the end (the 65's), but you'd
still
have just as much alternation and it would be more difficult to
understand.

Also, you'd need to change the starting word boundary token (\b) to
represent
your column letters optionally followed by the "$".


--ron

 

[M. Authement]

Thanks for the explanations Harlan. If I understand your more efficient
string, the ([A-Ha-h]?[A-Za-z]) will look for a two character reference
starting with A-H, and if that fails will look for a single character
reference of A-Z...is that correct?

M. Authement wrote...

Can someone explain this regular expression to me? I found it in some VBA
code for finding/altering a cell address within a string. I put spaces in
to break the expression apart into the parts (I think) I understand.

(?:[\^\])-/+*:,="[(]) (\$?) ([A-Z]{1,2}) (\$?) (\d{1,5}) ([^\d]|$)

...

The first, (?:...) looks like it's supposed to be any character that
could precede a valid cell address, but it doesn't include !, so this
regex won't match cell addresses that include the worksheet name. Also
it's got an innocuous bug: if hyphen, -, should be included in the
class, it should be the first character in the class. If the original
author intended it to be a character range from ) to /, then that would
have included * + , - . as well as ) and /, so the separate appearance
of those other characters is unnecessary.

The next bit matches zero or one literal dollar sign.

The next one or two letters.

The next another zero or one literal dollar sign.

The next one to five decimal numerals.

The last matches anything other than a decimal numeral or end-of-line,
but it's inclusive.

Much more efficient to use (remove the spaces)

\b (\$?) ([A-Ha-h]?[A-Za-z]|[Ii][A-Va-v]) (\$?) (\d{1,5}) \b

There's no need to parenthesize the break patterns, \b. Technically
this would match tokens like AX99999, which is NOT a valid cell address
but IS a valid name. Restricting the row number token to 1..65536 would
require something like

([1-5]?\d{1,4}|6([0-4]\d{3}|5([0-4]\d{2}|5([0-2]\d|3[0-6]))))

if you want to be a real stickler.

 

[Harlan Grove]

M. Authement wrote...
....

string, the ([A-Ha-h]?[A-Za-z]) will look for a two character reference
starting with A-H, and if that fails will look for a single character
reference of A-Z...is that correct?

Correct.

 

[Scott]

It's more a case of the ? representing an optional value.

A related explanation is the ? represents either 0 or 1 occurences of
whatever it follows.

Scott

Thanks for the explanations Harlan. If I understand your more efficient
string, the ([A-Ha-h]?[A-Za-z]) will look for a two character reference
starting with A-H, and if that fails will look for a single character
reference of A-Z...is that correct?

M. Authement wrote...

Can someone explain this regular expression to me? I found it in some VBA
code for finding/altering a cell address within a string. I put spaces in
to break the expression apart into the parts (I think) I understand.

(?:[\^\])-/+*:,="[(]) (\$?) ([A-Z]{1,2}) (\$?) (\d{1,5}) ([^\d]|$)

...

The first, (?:...) looks like it's supposed to be any character that
could precede a valid cell address, but it doesn't include !, so this
regex won't match cell addresses that include the worksheet name. Also
it's got an innocuous bug: if hyphen, -, should be included in the
class, it should be the first character in the class. If the original
author intended it to be a character range from ) to /, then that would
have included * + , - . as well as ) and /, so the separate appearance
of those other characters is unnecessary.

The next bit matches zero or one literal dollar sign.

The next one or two letters.

The next another zero or one literal dollar sign.

The next one to five decimal numerals.

The last matches anything other than a decimal numeral or end-of-line,
but it's inclusive.

Much more efficient to use (remove the spaces)

\b (\$?) ([A-Ha-h]?[A-Za-z]|[Ii][A-Va-v]) (\$?) (\d{1,5}) \b

There's no need to parenthesize the break patterns, \b. Technically
this would match tokens like AX99999, which is NOT a valid cell address
but IS a valid name. Restricting the row number token to 1..65536 would
require something like

([1-5]?\d{1,4}|6([0-4]\d{3}|5([0-4]\d{2}|5([0-2]\d|3[0-6]))))

if you want to be a real stickler.