Home » Forums » Open Source Learning Center » Question and Answer

How to include leading zeros when you combine columns

Submitted by Kelsey on 20 September, 2014 - 00:49

I have several columns that contain leading zeros (which I have figured out how to include). I am trying to combine the columns using "&," but every time I do the leading zeros are not included. For example I have the following:

Column I = 06
Column J = 059
Column K = 062622
Column L = 2004

In Column M I entered "=I1&J1&K1&L1" and I got 659626222004. However, what I want is 060590626222004.

How do I include the leading zeros when I combine the columns?

Thanks for the help!

‹ How to modify custom function. Help Link cells in different worksheets. When highlighting a cell in a summary worksheet,it displays other cells linked to it... ›
»

Use the Text function with

Submitted by mec1981 on 22 September, 2014 - 07:05.

Use the Text function with the concatenate ampersand as follows:

=TEXT(I1,"00")&TEXT(J1,"000")&TEXT(K1,"000000")&TEXT(L1,"0000")

Regards
Mark

»