Adding number in Excel 2003

[sivaranjan]

HI FRIENDS, i am using excel 2003 version. I have excel sheet with lot of number, I want total 7 digits in call. Some of numbers i have in 6 digits so I need to include 0 after “j” and Some of numbers is already has 7 digits.
e.g.
Numbers
j34654
j215487
j24875
j459875
j48546

Need to add “0” after “j”
j034654
j215487
j024875
j459875
j048546

Appreciate for your help

[MindSpace]

If the list starts at A2, here is your formula for B2:

=IF(LEN(A2)=6,LEFT(A2,1)&0&RIGHT(A2,5),A2)

Copy down the formula
copy the column B, and use Paste Special > Values to paste it right over the original data,If you want to toss out the original data, then delete the formulas in column B, no longer required.

[kelfro]

Some of numbers is already has 7 digits

What exactly do you mean by that . it is including 'J" ? if not then try the code given below

Code

Sub Add_Zero()
Dim Rng As Range, MyCell As Range
Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If Len(MyCell) <= 7 Then
MyCell.Replace What:="j", Replacement:="j0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next MyCell
End Sub

[sivaranjan]

Thanks for the help ,it works!

thanks for your time.