Anuj
Anuj

Reputation: 178

Insert a 0"zero" infront of number for specify number sequences

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

Answers (4)

Imran Malek
Imran Malek

Reputation: 1719

If you want to write formula then this would be better,

=REPT(0,4-LEN(A1))&A1

Upvotes: 0

Ankur
Ankur

Reputation: 151

Assuming you have codes in B column

if(len(b2)=3,concatenate("0",b2),b2)

Upvotes: 1

iamsankalp89
iamsankalp89

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

p._phidot_
p._phidot_

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

Related Questions