Cell Format Removes Zeros from 00.00.00

[jewkes6000]

I am trying to create a WBS which has the format of 00.00.00. I am trying to concatenate three columns with a period in between each 2 digit number. I'm using the formula =L2&"."&M2&"."&N2 in cell O2 (see attached image), but it will remove the zeros after the first period. So if I try to concatenate 00 05 00 with periods as in row 3 in my image, it will read 00.5.0 rather than 00.05.00. Can anyone help me with this please?

 

[jtakw]

Hi,

Are those values in your M and N column Text values, or Numbers formatted to show a leading 0 (zero) ?
Because if they're Text, you shouldn't have that problem.

 

[jtakw]

Well, no reply, so if those are numbers formatted to show leading zero, use O2 formula copied down:

Book3.xlsx
	L	M	N	O
1		Number	Number
2	00	00	00	00.00.00
3	00	05	00	00.05.00
4	00	05	10	00.05.10
5	00	05	20	00.05.20
6	00	05	30	00.05.30
7
8
9		Text	Text
10	00	00	00	00.00.00
11	00	05	00	00.05.00
12	00	05	10	00.05.10
13	00	05	20	00.05.20
14	00	05	30	00.05.30
Sheet1072

Cell Formulas
Range	Formula
O2:O6	O2	=L2&"."&TEXT(M2,"00")&"."&TEXT(N2,"00")
O10:O14	O10	=L10&"."&M10&"."&N10

 

[Phuoc]

Try this:

TEXT(L2*10000+M2*100+N2,"00\.00\.00")

 

[jewkes6000]

@jtakw - Yes, the numbers in columns M & N are Text format

 

[jewkes6000]

@jtakw - Your formula with the "Text" function worked perfectly! Thank you!

 

[jtakw]

You're welcome, thanks for the feedback.

If my O2 formula (with Text function) works on your data, but Not the O10 formula, then your values are Numbers, not Text.