CODEWITHSUNDEEP
excelexcel-formulavba
Xiodrade
Xiodrade

Reputation: 113

Concatenante and include leading zeroes

So currently I am pulling numbers from a spreadsheet that automatically formats their numbers into a certain amount of digits using special number formatting, so that it outputs with leading zeroes.

Example:

(zipcode formatting) 6 digits - 12345 becomes 012345

(Chinese (PRC) formatting) 6 digits - 12 becomes 000012

(Chinese (Taiwan) formatting) 3 digits - 0 becomes 000

My concatenate would put them together with periods between them; however, it does the base numbers instead of the formatting it should be doing.

=CONCATENATE(A1,".",B1,".",C1)

012345 000012 000 becomes 12345.12.0 instead of 012345.000012.000 Is there a function or something that will work to make it pull the converted special number instead of the one typed? It should ALWAYS be 6 digits.6 digits.3 digits.

Upvotes: 1

Views: 101

Answers (1)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

Try this:

= TEXT(12345,"######000000") & "." & TEXT(12,"######000000") & "." & TEXT(0,"###000")

Upvotes: 2

Related Questions