Reputation: 178
I have this data
Name | Code | Price
XXX 102 1000
YYY 4321 1150
ZZZ 202 1150
AAA 123 1000
I can now Add concatenate and Add 0 in front of Code which makes
0102
04321
0202
0123
Now here the problem lies. I dont want that 0 in front of 4321 . I want 0 only infront of 3 digit numbers not more than 3 digit.
Upvotes: 2
Views: 74
Reputation: 1719
If you want to write formula then this would be better,
=REPT(0,4-LEN(A1))&A1
Upvotes: 0
Reputation: 151
Assuming you have codes in B column
if(len(b2)=3,concatenate("0",b2),b2)
Upvotes: 1
Reputation: 4739
Right click on Column, go to Format cell-->Custom and write 0000 in the type and click on Ok
Simplest and easy solution
Upvotes: 3
Reputation: 1950
Assuming the '102' data is located at B2, just type :
=IF(len(B2)<=3,"0"&B2,B2)
will do. Alternatively, using concatenate()
function you may do it like this :
=IF(len(B2)<=3,CONCATENATE("0"&B2),B2)
Upvotes: 1