Forum Discussion

Kimberky's avatar
Kimberky
Copper Contributor
May 11, 2020
Solved

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 ...
excel
  • mtarler's avatar
    mtarler
    May 11, 2020

    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"), ...)

     

mtarler's avatar
mtarler
Silver Contributor
May 11, 2020

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"), ...)

 

Resources