Ask Experts Questions for FREE Help!
Ask    ||    Answer
 
  Advanced  
 

Ask QuestionsprogressAnswer QuestionsprogressBuild ReputationprogressBecome an Expert
 
Free Answers in 3 Easy Steps

Register Now
3 Steps

At Ask Me Help Desk you can ask questions in any topic and have them answered for free by our experts. To ask questions or participate in answering them you must register for a free account. By registering you will be able to:
  • Get free answers from experts in any of our 300+ topics.
  • Accept money for answers that you provide.
  • Communicate privately with other members (PM).
  • See fewer ads.

Home > Computers & Technology > Software > Spreadsheets   »   padding numbers in excel

 
Thread Tools Search this Thread Display Modes
Question
 
 
#1  
Old Apr 10, 2008, 01:55 AM
joed123
New Member
joed123 is offline
 
Join Date: Apr 2008
Posts: 1
joed123 See this member's comment history on his/her Profile page.
padding numbers in excel

I have about 500,000 cells on a spreadsheet that all follow a letter and number pattern. There are two patterns that look like this:
y124f07 and h67g02.

So the first is:

letter, number, number, number, letter, number, number

the second is:

letter, number, number, letter, number, number

I need all of the patterns to follow the first pattern, so any time there are only two numbers between the two letters I need to ad a "0" before the first two numbers ie. change h67g02 to h067g02 without changing y124f07 and without messing up the last two numbers (07 or 02). Please help. thanks Joe

Reply With Quote
 
     

Answers
 
 
Old Apr 10, 2008, 04:53 AM   #2  
Engineering & Electronics Expert
KeepItSimpleStupid is offline
 
KeepItSimpleStupid's Avatar
 
Join Date: Aug 2007
Posts: 9,154
KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.
A couple of hints:

1) Macro time
2) The length of the string determines if the cell is affected.
3) You can then split the string and add the character zero.
  Reply With Quote
 
     
 
 
Old Apr 10, 2008, 06:43 AM   #3  
Engineering & Electronics Expert
KeepItSimpleStupid is offline
 
KeepItSimpleStupid's Avatar
 
Join Date: Aug 2007
Posts: 9,154
KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.
Here is the basic command you need to embed ina macro:

=CONCATENATE(MID(C3,1,1),0,MID(C3,2,2),MID(C3,4,3) )

Where C3 is a cell with a length of 6, len(C3)=6; e.g. h67q02

It will make it h067q02

Doing it a bit further:

=IF(LEN(C3)=6,CONCATENATE(MID(C3,1,1),0,MID(C3,2,2 ),MID(C3,4,3)),C3)

Will leave the cell alone if the lenght is not equal to 6.

You may have to use a macro and create a totally different sheet because circular references are not allowed.
  Reply With Quote
 
     

Your Answer
Email me when someone replies to my answer
Join Login





Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

 
Similar Sponsors


Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page

Similar Threads
phone numbers
(3 replies)
Excel: Convert numbers to date
(1 replies)
Laminate flooring double padding?
(3 replies)
distributor numbers help
(0 replies)
MS Excel spreadsheet changing numbers
(1 replies)

Search this Thread

Advanced Search

Bookmarks

Sponsors



Copyright ©2003 - 2009, Ask Me Help Desk.
All times are GMT -8. The time now is 02:56 AM.