I want to fill cells by numbers which may have various length (common is 4 or 5), then i wanna to add number prefix depending on length of value by conditional formatting e.g. 1111 (length is 4) should be formated to "20001111" (adding prefix 2000) and e.g. 11111 (length is 5) should be formated to "20011111". So difference is just how many additional "0" will be added by conditional formatting. How can i do this? I know that i can use conditional statements in formulas and i've tried it already. I couldnt find how can i get value length and how can i use it in conditional statements.

CodePudding user response：

This is the formula you need: =LEFT("20000000",8-LEN(A1))&A1

Please analyze this to learn more about formulas.