I have a row where all strings have the identical format (XX-#####-##) in which Xs are 2 upper case alphabet letters and the #s are integers.

Short example, see the doc for more KC-30000-00 | KC-30000-01 | KC-30000-02 | KC-30000-05 | KC-30000-99 | KC-33821-05

My plan is to run a formula (let's say add 10%) in the next row of all columns except for the columns with strings in the range of "KC-30000-00" to "KC-30000-98".

I tried to define the range using the formula below (and a few others) but it still includes -99 and I couldn't improve it =ArrayFormula(IF(COLUMN(A1:1)=1,"HEADER",REGEXMATCH(A1:1,"KC-30000-[0-9][0-9]$")))

There are already many columns and it will grow so it's best to have a flexible formula in A3.

The doc has a few columns as a sample - https://docs.google.com/spreadsheets/d/1ytBameD5SrT6aNxed2sycKMB8BwMr8OVY5N1dO2vTjU/edit?usp=sharing

Please also feel free to suggest a better subject for this question to help others, perhaps.

Thank you in advance

  • 2
    Your link permission is "View Only" without even the ability to make a copy or to copy and paste data; so there is no starting point for the volunteer contributors here. Please either open the sample sheet ("Anyone with the link" ... "Editor") or, at a minimum, remove the copy lock. Otherwise, as is already happening here, most volunteers will simply move on to the next post.
    – Erik Tyler
    Jan 27, 2022 at 13:38
  • That was unintentional, I adjusted the sharing settings
    – YBB
    Jan 27, 2022 at 15:19

1 Answer 1


See my comment to your post.

That said, eyeing it, this should work (in, say, A3 of an otherwise empty Row 3):

=ArrayFormula({"Header", IF((B1:1="")+(REGEXMATCH(B1:1,"30000-[^(99)]")),,"do something")})

The REGEXMATCH condition reads "30000 dash [anything that is not exactly the grouping 99]."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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