REGEX - Data Validation

Question

I have the following Google Spreadsheet.

When users enter a name into any cell of column A of the sheet named "Unit Standards" I want them to enter that name in a particular format. That is, with the surname first in uppercase then a comma, then the first name in title case then if they are known by a different name that name to be title case in brackets e.g.

BUSH, George
TRUMP, Donald
CLINTON, William (Bill)
CARTER, James (Jimmy)
SMITH-JONES, John
ZETA-JONES, Catherine (Kate)

Someone else helped me with the following code which I have added to column A as a Data Validation.

=REGEXMATCH(A10,"^[A-Z]+\b[',']\s[A-Z]{1}[a-z]+\b(\s([A-Z][a-z]+\b))?$")

You can see by the following image that some of the inputs are in violation so the formula is not quite right.

I would appreciate some help with the code.

Guilherme · Accepted Answer · 2019-12-09 11:21:06Z

2

This regex matches all the cases you proposed:

[A-Z][A-Z' ]+(-[A-Z' ]+)*, [A-Z][a-z]+( \([A-Za-z]+\))?

EDIT: Added the cases proposed in comment.

edited Dec 9, 2019 at 11:21

answered Dec 9, 2019 at 2:09

Guilherme

4564 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

McChief Over a year ago

Thank you - it now works for every possibility except ZETA-JONES, Catherine (Kate)

Guilherme Over a year ago

Is because zeta-jones have a dot.

Guilherme Over a year ago

Now it includes the dot

McChief Over a year ago

I'm impressed - that works perfectly. Thank you very much for your time and input.

McChief Over a year ago

Sorry - I have come across another 3 scenarios TE AMO, Eric, also O'BRIEN, Patrick and SMITH, Carl (CJ)..Would you mind helping me with these? And sorry again, the dot was a mistake

player0 · Accepted Answer · 2019-12-09 09:27:17Z

1

try:

=ARRAYFORMULA(REGEXMATCH(A1:A10, "^([A-Z' -]+), ([A-Z]{1}[a-z]+)( \([A-ZA-z]+\))?$"))

answered Dec 9, 2019 at 9:27

player0

131k14 gold badges91 silver badges149 bronze badges

Collectives™ on Stack Overflow

REGEX - Data Validation

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related