Solved: Add extra characters in all cells in column

[NY2DR]

Hello all,
Here is my situation. I have about 5,000 rows in a column that has 10 or less characters in it. I would like to select those rows which have less than 10 characters and add three zeros in front of it to make it equal to 10
Example: 12345678L --> 012345678L 123456789Q --> STAYS THE SAME 12345H --> 000012345H 123456G --> 000123456G
Any solutions?
I was using this: =IF(LEN(A22)=7,"000"&A22,A22) But this takes too long and is tedious changing then len value every week.
Thanks

[Bob Phillips]

=LEFT(REPT("0",10),10-LEN(A1))&A1

[NY2DR]

BEAUTIFULL!!!!!!!!!!!!!!!!!!!:rotlaugh:

Now how would this work in a macro.
I get these sheets every week. I use the formulas on one column (H), copy it, then paste the value back to the original colomn(C).

This gets monotonous when I have space in between rows, and then I have to find and delete those spaces which have all zeros.

It will be greatly appreciated!

[lucas]

Be sure to mark your thread solved using the thread tools at the top of the page.

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim iLastRow As Long

With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Range("H1").Resize(iLastRow).FormulaR1C1 = "=IF(RC1="""","""",LEFT(REPT(""0"",10),10-LEN(RC1)))&RC1"
.Range("H1").Resize(iLastRow).Copy
.Range(TEST_COLUMN & "1").PasteSpecial Paste:=xlPasteValues
.Range("H1").Resize(iLastRow).ClearContents
End With

End Sub

[NY2DR]

NICE!:friends:

I LIKE IT!:thumb

Saves me some headache!

Thank you!
:beerchug: