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!
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuinnDexCommented:
i picked up this one a while back

for 2007 and after

\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b


for older versions

\$?\b([A-Z]|[A-H][A-Z]|I[A-V])\$?([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([:\s]\$?\b([A-Z]|[A-H][A-Z]|I[A-V])\$?([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)?


credits to DennisCPA
0
aikimarkCommented:
I think you need to do this in two parts.  First, include capture groups with your pattern:
^([A-Za-z]{1,3})(\d{1,7})$

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.