add zero series of number formula

[Tripleseas]

Hello,

i want a formula that can add leading zero after a serie of number in excel. the maximum to fill is 7 digits as shown below : thank

Classeur3
	A	B
1	number	Number edited
2	315	315 000
3	312	312 000
4	435	435 000
5	536
6	634
7	1 179	1 179 000
8	444
9	510
10	133
11	424
12	692
13	517
14	1 318
15	1 269
16	378
17	155
18	176
19	429
20	502
21	702
22	551
23	238
24	1 070
Feuil1

 

[Sufiyan97]

hi Still not getting what you want?

as per column C or D?

Book4
	A	B	C	D	E
1	number	Number edited
2	315	315000	315000	0000315
3	312	312000	312000	0000312
4	435	435000	435000	0000435
5	536		536000	0000536
6	634		634000	0000634
7	1179	1179000	1179000	0001179
8	444		444000	0000444
9	510		510000	0000510
10	133		133000	0000133
11	424		424000	0000424
12	692		692000	0000692
13	517		517000	0000517
14	1318		1318000	0001318
15	1269		1269000	0001269
16	378		378000	0000378
17	155		155000	0000155
18	176		176000	0000176
19	429		429000	0000429
20	502		502000	0000502
21	702		702000	0000702
22	551		551000	0000551
23	238		238000	0000238
24	1070		1070000	0001070
25
Sheet1

Cell Formulas
Range	Formula
C2:C24	C2	=A2*1000

 

[jasonb75]

It looks like the spaces in the example are done with custom formatting, so this formula should work correctly.

=--LEFT(A2&"0000000",7)

If you have actual spaces in the formula then it will most likely not work.

edit: ignore this, I think @Sufiyan97 is closer.

For future reference, those are trailing zeros, not leading (which go in front of the number as per column D in the post above).

 

[Tripleseas]

t

hi Still not getting what you want?

as per column C or D?

Book4
	A	B	C	D	E
1	number	Number edited
2	315	315000	315000	0000315
3	312	312000	312000	0000312
4	435	435000	435000	0000435
5	536		536000	0000536
6	634		634000	0000634
7	1179	1179000	1179000	0001179
8	444		444000	0000444
9	510		510000	0000510
10	133		133000	0000133
11	424		424000	0000424
12	692		692000	0000692
13	517		517000	0000517
14	1318		1318000	0001318
15	1269		1269000	0001269
16	378		378000	0000378
17	155		155000	0000155
18	176		176000	0000176
19	429		429000	0000429
20	502		502000	0000502
21	702		702000	0000702
22	551		551000	0000551
23	238		238000	0000238
24	1070		1070000	0001070
25
Sheet1

Cell Formulas
Range	Formula
C2:C24	C2	=A2*1000

Thanks its column C. worked great

 

[Tripleseas]

It looks like the spaces in the example are done with custom formatting, so this formula should work correctly.

=--LEFT(A2&"0000000",7)

If you have actual spaces in the formula then it will most likely not work.

edit: ignore this, I think @Sufiyan97 is closer.

For future reference, those are trailing zeros, not leading (which go in front of the number as per column D in the post above).

thank you for the information sufyan method worked great. thanks

 

[Sufiyan97]

Hello,
the maximum to fill is 7 digits as shown below : thank

If you have any change that the number in column A is more than 5 digit than my suggestion will not meet you condition of max 7 digits,
In that case jasonb's formula will work perfect.

 

[jasonb75]

In that case jasonb's formula will work perfect.

Not quite, it would have to be changed to

=--LEFT(A2&"000",7)

my original suggestion would have added too many zeros to any numbers less than 4 digits.

 

[Sufiyan97]

Not quite, it would have to be changed to
=--LEFT(A2&"000",7)

Just for curiosity why should we change?

While it is already taking 7 digits from left.

 

[jasonb75]

If you look at the first examples the result should only be 6 digits. Without the change the formula would have incorrectly returned 7 for those.

Personally, I think that your method of multiplying by 1000 is more accurate in line with what appears to be required rather than what was asked for. With the wording of the OP's question I had filled everything to 7 digits and not noticed the error until I looked at your suggestion after posting mine.