Losing leading zeros in concatenate formula

mjrandall

New member
Joined
May 6, 2014
Messages
2
Reaction score
0
Points
0
I have date of birth data in columns ABC, with A and B formatted to show leading zeros.

Used the following formula to combine data into column D in a required format:
=A2&"/"&B2&"/"&"19"&C2

PROBLEM: the leading zeros in A and B are not showing up in the formula in Column D. Any solutions?

ABCD
0501485/1/1948
0504685/4/1968
13117213/11/1972
 

Attachments

  • Screen Shot 2014-05-06 at 3.56.21 PM.png
    Screen Shot 2014-05-06 at 3.56.21 PM.png
    23.4 KB · Views: 15

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try

=TEXT(A2,"00")&"/"&TEXT(B2,"00")&"/"&"19"&TEXT(C2,"00")

or

=TEXT(DATE(C2,B2,A2),"dd/mm/yyyy")
 
Top