Forum Discussion
Using custom data validation with regex on cells
Hi..
Using windows 10. Microsoft Excel 2016/2019/365.
I created custom data validation for use in google sheets.. After i realized i need it for Microsoft excel, i tried to look up a how to for that.
I didn't found any way to achieve what i need.
I need the cells to be formatted as Text( cant have them as date,numer,list etc).
Here are some of the functions i use in Sheets:
To choose only yes or no
=REGEXMATCH(Sheet1!A2:A1000,"\b(yes|no)\b")
To dont allow special characters
=ArrayFormula(REGEXMATCH(Sheet1!B2:B1000,"^[a-zA-Z]*$"))
Only numbers:
=ArrayFormula(REGEXMATCH(Sheet1!G2:G1000,"^[A-Z0-9]*$"))
Again i need the cell to be text only, thats the reason i cant use the predefined verifications.
I tried to google for a solution but couldn't found any.
Any help will be appreciated!
3 Replies
I am pretty sure there is no support for regular expressions within Excel formulas. 365 insider beta, in particular, will allow you to write functions that perform the tasks you require but not by using Regex. For a modest cost it is possible to license libraries that use Regex.
FastExcel V4 SpeedTools (decisionmodels.com)
The following worked for me (using the addin)
= Rgx.AMATCHES2("\b(yes|no)\b", string) = Rgx.AMATCHES2("^[a-zA-Z]*$",string)Thank youPeterBartholomew1
I will probably stick to google sheets if that's the case.
The document need to be given to other people to use And if i will use 365 insider or some other solution, it wont work those people probably. Cause they will have other versions of office.
I see you point. If you share with people that use Google sheets it make sense to retain compatibility. The particular add-in that I showed comes with a run-time version that can be shipped to clients (involves a one-off fee).
For me the issues are very different. Having once described the A1 notation as an abomination that should never have entered the world of the electronic spreadsheet, I am only too happy to enter the world of dynamic arrays and build solutions as if they were regular code.
