add a leading zero

[ksquared]

Hi,

I need to add a leading "zero" if the contents of the cell = 3 digits so that all of the cells contain 4 digits.

Some of the cells have 3 digits and some of them already have 4.

Col A before
112
1112
520
567
3345

Col A after append
0112
1112
0520
0567
3345

The cells are set up as "text" right now but I can change to another format. I'm using Microsoft Office Excel 2007

Thanks in advance for your help.

K2

 

[VoG]

Try formatting the cells as Custom

0000

 

[Legacy 185660]

Go to format, click custom and copy this into the format: 000#
this will take
1
11
111
1111

and make them into
0001
0011
0111
1111

 

[Joe4]

Do you just need to change them for display purposes, or for other purposes (i.e. calculations, etc)?

The reason why I ask is changing their format changes their appearance, but will not change the underlying values. So if you are trying to do calculations on those values, you may not get the results you expect.

 

[ksquared]

Thanks. Still haivng trouble.

I highlighted Col A
selected "Custom" form the Format Cells menu.
input "000#" into the Type: field (I also tried "0000")
and clicked "ok"

But it didn't change anything. The cells with 3 digts are still 3 digits.

What am I doing wrong?

 

[ksquared]

Re: add a leading zero

--------------------------------------------------------------------------------
Do you just need to change them for display purposes, or for other purposes (i.e. calculations, etc)?

The reason why I ask is changing their format changes their appearance, but will not change the underlying values. So if you are trying to do calculations on those values, you may not get the results you expect. - Joe4

I need to change the underlying values. I feed the values into another program using a CSV file and the other program requires 4 digits.

 

[T. Valko]

Hi,

I need to add a leading "zero" if the contents of the cell = 3 digits so that all of the cells contain 4 digits.

Some of the cells have 3 digits and some of them already have 4.

Col A before
112
1112
520
567
3345

Col A after append
0112
1112
0520
0567
3345

The cells are set up as "text" right now but I can change to another format. I'm using Microsoft Office Excel 2007

Thanks in advance for your help.

K2

Pretty easy if you use another column.

If the data is in column A enter this formula in column B:

=IF(LEN(A2)=3,"0"&A2,A2)

 

[Joe4]

Actually, in saving your Excel file as a CSV format, it should maintain the format. So the recommendations given previously should work for you.

If you were doing some string calculations on the spreadsheet, and needed the value to actually have the zeroes in the underlying value, you could use the TEXT function, i.e.

=TEXT(A1,"0000")

 

[T. Valko]

Pretty easy if you use another column.

If the data is in column A enter this formula in column B:

=IF(LEN(A2)=3,"0"&A2,A2)

Even easier:

=TEXT(A2,"0000")

 

[Jonmo1]

I need to change the underlying values. I feed the values into another program using a CSV file and the other program requires 4 digits.

In that case, formatting will do you no good...

You'll need a formula in an adescent cell.

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