• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

regex to validate excel 2007 range input

hey guys,

i've got a regex question already! ha

i've got a custom input box. i want the user to enter a valid range address for excel 2007. so i've got this pattern ^[A-Za-z]{1,3}\d{1,7}$ which does a check if it is a valid excel 2007 range.

i do understand that my pattern is not completely correct - what if the user entered ZZZZ0, my pattern would pass but it's not a valid excel 2007 range address

what is the correct pattern i should use to limit the range to A1 to XFD1048576?

thanks guys!
2 Solutions
i picked up this one a while back

for 2007 and after


for older versions


credits to DennisCPA
I think you need to do this in two parts.  First, include capture groups with your pattern:

You can/should test the pattern before parsing.

If a valid pattern, with Match(0), you should have two submatches.  Invoke the following code to validate the cell address:
Select Case oMatches(0).SubMatches(0)
    Case "A" To "XFD"
    Case Else
        msgbox "Invalid column value in cell address.  Should be in the range A-XFD"
End Select
Select Case oMatches(0).SubMatches(1)
    Case 1 To 1048576
    Case Else
        msgbox "Invalid row value in cell address.  Should be in the range 1-1048576"
End Select

Open in new window

developingprogrammerAuthor Commented:
hi QuinnDex, thanks for posting Dennis' regex! = ) it work absolutely perfect!! really quite super brilliant.

hi aikimark! thanks for sharing your solution! it's really super insightful!

the reason why i marked aikimark's solution as the best solution is because it's a lot more readable and easy to understand - Dennis' regex works like magic but i can't really understand it (due to my lack of abilities no doubt!) and thus i think in terms of readability it's always better to have something we can understand and thus verify = )

thanks guys!! = ))
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now