Leading zero's in cells

[dwrowe001]

Hi,
I am having the hardest time trying to get leading zeros to appear in cells...
I'm trying to append of concatenate digits from one cell with digits from another cell:

	A	B	C	D	E	F
1	03	15	17	20	32
2
3
4

<tbody>
</tbody>

I want for example to get "03" in A1 and the "1" in B1 to combine in cell A2.
I have tried following formulas in A2:
=A1&LEFT(B1,1)... which results in 31 appearing in A2.
=LEFT(A1,2)&LEFT(B1,1)... which results in 31 appearing in A2.
strange thing is, if I just put =A1 in A2, then "03" appears in A2. I've changed cell formatting in A2 to custom and "000". How do I combine the "03" in A1 and the first digit in B1 to go into A2?...
Not sure if it's important or not, I'm using Excel 2016.
Doesn't seem right that doing this is so hard? Am I way wrong with the formulas I've tried?
Dave

 

[dreid1011]

Re: Leading zero's in cells -- HELP!!

You could try this with combined with the 000 custom format:

=(A1&LEFT(B1,1))*1

 

[dwrowe001]

Re: Leading zero's in cells -- HELP!!

You could try this with combined with the 000 custom format:

=(A1&LEFT(B1,1))*1

Yes, that works... but what if in B1 instead of 15 you have 03? It worked when the first digit in B1 is anything but 0..

 

[dwrowe001]

Re: Leading zero's in cells -- HELP!!

You could try this with combined with the 000 custom format:

=(A1&LEFT(B1,1))*1

What does the *1 do??

 

[Peter_SSs]

Re: Leading zero's in cells -- HELP!!

From what you describe, I think A1 actually only contains the number 3 but is Custom formatted as "00". I think B1 is similarly Custom formatted as "00".
Try this formula

=TEXT(A1,"00")&LEFT(TEXT(B1,"00"),1)

 

[dreid1011]

Re: Leading zero's in cells -- HELP!!

What does the *1 do??

Since LEFT is a string function, it returns a text string. The *1 converts the number stored as text to a real number so it works with the custom format. I did not test extensively, so give Peter's formula a go.

 

[dwrowe001]

Re: Leading zero's in cells -- HELP!!

Excellent, thank you both for your help... They both work depending on positions of my numbers.. thanks so much!!

 

[Peter_SSs]

Re: Leading zero's in cells -- HELP!!

You are very welcome.