Forum Discussion
Concatenating custom formatted cells
Cells are formatted to keep 6 digits, concatenating only brings in the digits without zeros....i.e. 000272 is bringing in 272 when concatenating. How do I keep the 6-digit format when concatenating the cells?
Kimberky custom formatting is only a viewing thing and has no effect on the value. So forcing a cell to display 6 digits like 000272 means you will see 000272 but the value is still only 272 and formulas act on the value. If you want to force those added zeros in a concate function you can try:
=CONCAT(RIGHT("000000"&A1,6), RIGHT("000000"&B1,6), …)
OR
=CONCAT(TEXT(A1,"000000"),TEXT(B1,"000000"), ...)
2 Replies
- Type in 272 and press CTRL + 1 > Click on Custom > Clear General and type in 000000 (six zeros) > click OK
That's all Kimberky custom formatting is only a viewing thing and has no effect on the value. So forcing a cell to display 6 digits like 000272 means you will see 000272 but the value is still only 272 and formulas act on the value. If you want to force those added zeros in a concate function you can try:
=CONCAT(RIGHT("000000"&A1,6), RIGHT("000000"&B1,6), …)
OR
=CONCAT(TEXT(A1,"000000"),TEXT(B1,"000000"), ...)
