Forum Discussion
Adding 1 to Every Number in Formula String
Hi All,
I am working on many workbooks and for all of them, I am using conditional formatting for hundreds of cells. Specifically, I am using 7 different cell ranges, and the cells themselves are always one cell apart. For example, this is for one of the ranges (with hundreds more references in this range and in each range):
B$4:AS$4,B$9:AS$9,B$14:AS$14,B$19:AS$19
Is there a formula I can use to add +1 to every number, so I don't have to do it manually? I can't find an answer anywhere. Here is what I would need:
B$5:AS$5,B$10:AS$10,B$15:AS$15,B$20:AS$20
Thank you for the help.
2 Replies
=TEXTJOIN({":".","},,LEFT(TEXTSPLIT(A4,{":".","}),SEARCH("$",TEXTSPLIT(A4,{":".","})))&TEXTNACH(TEXTSPLIT(A4,{":".","}),"$")+1)
With Excel for the web or Office 365 you can use this formula. The formula is filled down from cell A5. Perhaps this is what you are looking for.
You may apply conditional formatting to the range $B$1:$AS$20 and into conditional formatting rule formula add condition ( MOD( ROW(),5 ) = 0 ).
Or like, entire picture is not clear.
