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 this

Sub AddZero()
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Value = String(4 - Len(.Value), "0") & .Value
    End With
Next i
End Sub

 

[Joe4]

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

Sure it will! I guess it may depend on how you are creating your CSV file, but if you are just doing a "SaveAs" from Excel, it WILL maintain the formatting (i.e. the leading zeroes will be saved to your CSV). Try it, you'll see!

Note: Don't use Excel to view your final CSV, as it will drop the leading zeroes upon re-opening in Excel. Use a Text Editor program (NotePad, etc.) to view the CSV file.

 

[ksquared]

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

and

=REPT(0,4-LEN(A1))&A1 also worked.

Since I am already using a macro to modify the data in this very messy spreedsheet I will try VoG's program when I edit my macro.

Thanks for all the responses. Even if I couldn't get a few of them to work, I can always file it away to try with something else.

I have another quesiton but since it's a differant kind of thing I will post a new thread.

PS:I start with an Excell, run my macro to reformat the data and than save it as a CSV. With the CSV still open, I run another macro to finish up reformating, save it when done and than import from my other program. I didn't know I could use Notepad to view it. Thanks

 

[Joe4]

I didn't know I could use Notepad to view it.

I always tell people NEVER use Excel to view or edit a CSV file. It will drop the leading zeroes. So if you save the CSV in Excel after opening it, you will drop the leading zeroes from the CSV file.

I often have people tell me that they have CSV files that are missing leading zeroes. I ask them if they are viewing it in Excel or NotePad. They always say Excel. I say try NotePad, and lo and behold, the see the leading zeroes are really there! They are just being dropped when opening in Excel.

Because of this, I have reset my computer defaults to open all CSV files in UltraEdit (a robuest Text Editor we use) instead of Excel.