0

I need a help for regexp writing to validate format:

any_positive_integer@any_text,any_positive_integer@any_text, .....

To be exact, "any_positive_integer@any_text" separated by commas and inside, number and text separated by "@".

Any idea and help for regexp doing it?

1

The quick start tutorial at https://developers.google.com/apps-script/quickstart/macros shows how to write a JavaScript function in the App Script editor and call it from a Google spreadsheet.

In this case the function can use a regexp such as

/\d+@[^,]+(,\d+@[^,]+)*/

That means one or more digits followed by an @ and one or more non-comma characters, followed by zero or more repetitions of a comma and the same pattern.

That pattern will match anywhere in the given string. If you want to match against the entire input string, i.e. not allow other characters before or afterwards, then start the pattern with /^ and end it with $/ instead of plain / and /. If you then want to allow white space before or after the pattern, you can add \s+ right after the ^ and/or before the $.

You'll want to call a JavaScript regexp method like test() to test a string against the pattern. Example using a simpler pattern (just comma separated numbers):

function check(input) {
  return /^\d+(,\d+)*$/.test(input);
}

There are web sites containing tools for interactive testing of regular expressions. That's a good way to go since these things can be tricky and hard to read. Build it up slowly and test it thoroughly. I didn't test the expressions above. You should test that they meet your needs.

Edit: Per https://support.google.com/docs/answer/3098292 you don't need to call JavaScript to use regular expressions in Google spreadsheets. Example formula:

=REGEXMATCH(A1, "^\d+$")

using a simpler pattern that matches a string of digits.

  • Many thanks. To be correct .... working fraze ^\d+@[^,]+(,\d+@[^,]+)*$. I used with Data Validation and formula =if(isblank(range);true;REGEXMATCH(TO_TEXT(range);"^\d+@[^,]+(,\d+@[^,]+)*$")) – kraf101 Mar 9 '15 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.