TechArena Community Adding number in Excel 2003

#1
08-12-2008
 Member Join Date: Aug 2006 Posts: 148

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

__________________
The Gaming Machine:
Processor: Core 2 Duo E6400
Motherboard: Asus P5W DH Deluxe
RAM: 2GB XMS2 Corsair PC2-6400
Video: ATI Radeon X1900XT 512MB w/ Arctic Accellero
Drive: 74GB "WD Raptor" 10000RPM
Sound: Creative SoundBlaster X-Fi
Watercooling: *Soon* Zalman Reserator
#2
08-12-2008
 Member Join Date: Feb 2008 Posts: 1,848
Re: Adding number in Excel 2003

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.
#3
08-12-2008
 Member Join Date: Apr 2008 Posts: 2,001
Re: Adding number in Excel 2003

Quote:
 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

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
#4
08-12-2008
 Member Join Date: Aug 2006 Posts: 148
Re: Adding number in Excel 2003

Thanks for the help ,it works!

__________________
The Gaming Machine:
Processor: Core 2 Duo E6400
Motherboard: Asus P5W DH Deluxe
RAM: 2GB XMS2 Corsair PC2-6400
Video: ATI Radeon X1900XT 512MB w/ Arctic Accellero
Drive: 74GB "WD Raptor" 10000RPM
Sound: Creative SoundBlaster X-Fi
Watercooling: *Soon* Zalman Reserator

 Tags: excel