Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 08-12-2008
Member
 
Join Date: Aug 2006
Posts: 148
Adding number in Excel 2003

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
__________________
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
Reply With Quote
  #2  
Old 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.
Reply With Quote
  #3  
Old 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

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

Thanks for the help ,it works!

thanks for your time.
__________________
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags:



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Adding number in Excel 2003"
Thread Thread Starter Forum Replies Last Post
Adding digit in front of number in cell in Microsoft Excel Thedevotee MS Office Support 2 07-02-2012 07:45 PM
Cell format errors appears in Excel 2003 but not in Excel 2000 @nki$achi MS Office Support 1 24-01-2012 05:00 PM
Adding a terminal server to SBS 2003. Frragrant Small Business Server 6 01-06-2011 12:47 AM
Exporting from Project 2007 to Word 2003 or Excel 2003 Jasper W Microsoft Project 5 20-04-2009 11:52 PM
Excel 2003 crashes using save as excel 97-excel 2003 & 5.0/95 .xls lUSTrOUSo MS Office Support 2 16-04-2007 10:56 PM


All times are GMT +5.5. The time now is 01:44 AM.