Inserting a “–” or a “0” using REGEX?

You'll have to double check that the formula below meets all your criteria and actually works. There are multiple ways to do this. This one is the simplest of the ones I came up with.

cell H2 =LEFT(REGEX.EXTRACT(D2,"[a-zA-Z]+")&"-",2)&RIGHT("0"&REGEX.EXTRACT(D2,"[0-9]+"),2)

It does the following:

1. Extracts the letter(s)
2. Appends a "-" onto them/it
3. Truncates the resulting string to the leftmost two characters. If there had been only one letter it is now a letter and a "-". If it was two letters, the "-" gets removed, leaving the two characters.
4. Extracts the number(s)
5. Prepends a "0" onto them/it
6. Truncates the resulting string to the rightmost two characters. If there had been only one number it is now a "0" and a number. If it was two numbers, the leading "0" gets removed, leaving the two numbers.
7. Sticks those two strings together.

stfflspl wrote:

The following regular expression can be used for the purpose of validation:

^[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}$

If I read that right, the leading ^ and trailing $ are the same as where we use quotes. The first part of the code is, in order

1. One or two characters from A-Z
2. One number 0-9
3. Zero or one character A-Z or 0-9

That corresponds to the 6 possibilities you listed. I think the more recent formula covers all those bases.

I find them on the Internet, it is not just a Numbers thing. There are often multiple expressions that do the same things; not everything you find will be the best. One quick resource is https://regexlib.com/CheatSheet.aspx or the top level which is https://regexlib.com/Default.aspx

Ahh, the problem with numbering systems: exceptions to the rules. That one doesn't fit the specified pattern. But no worries. Remember I said I had come up with several ways to do the same thing? Luckily, one of them handles this situation.

=LEFT(TEXTBEFORE(C3,REGEX("[0-9]"))&"-",2)&RIGHT("0"&TEXTAFTER(C3,REGEX("[a-zA-Z]+")),2)

The left part of the string is determined by location of the first number. Everything to the left of the first number is part of the left string.

The right part of the string is everything to the right of the first set of alpha characters. It is the first non-alpha character plus everything to the right of it.

I wrote my formula for the first table, not the second. The only difference would be to put the formula in G3 and change all D2 references to C3

If there are too many exceptions, you might have to go back to handling each variation separately. What are some of the other variations?

All but the third one, A9A. Result of the formula is A-9A not A90A.