If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Limit Number in cell to 5 Digits
I have a Zip Code Table with numbers with 9 digits or more showing up.
How do I go about only allowing the field to show the first 5 numbers and dropping the rest after 5?  Brian 
Ads 
#2




Limit Number in cell to 5 Digits
Public Sub ProcessData()
Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow .Cells(i, "A").Value = Val(Left$(.Cells(i, "A").Value, 5)) Next i End With End Sub  HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "bdehning" > wrote in message ... >I have a Zip Code Table with numbers with 9 digits or more showing up. > > How do I go about only allowing the field to show the first 5 numbers and > dropping the rest after 5? >  > Brian 
#3




Limit Number in cell to 5 Digits
Excuse me for being limited in knowledge about Macros.
Do I need to change anything in the macro you provided to work in my spreadsheet. I created the macro and saved it. I then highlighted the cells I wanted to change and then ran the macro but it didn't appear to be doing what I wanted. Maybe I should have waited longer? Am I doing somthing terribly wrong. Brian  Brian "Bob Phillips" wrote: > Public Sub ProcessData() > Dim i As Long > Dim iLastRow As Long > > With ActiveSheet > > iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row > For i = 1 To iLastRow > .Cells(i, "A").Value = Val(Left$(.Cells(i, "A").Value, 5)) > Next i > > End With > > End Sub > > > >  > HTH > > Bob > > (there's no email, no snail mail, but somewhere should be gmail in my addy) > > "bdehning" > wrote in message > ... > >I have a Zip Code Table with numbers with 9 digits or more showing up. > > > > How do I go about only allowing the field to show the first 5 numbers and > > dropping the rest after 5? > >  > > Brian > > > 
#4




Limit Number in cell to 5 Digits
You may need to change "A" to the actual column that you want. No need to
select them.  HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "bdehning" > wrote in message ... > Excuse me for being limited in knowledge about Macros. > > Do I need to change anything in the macro you provided to work in my > spreadsheet. > > I created the macro and saved it. I then highlighted the cells I wanted > to > change and then ran the macro but it didn't appear to be doing what I > wanted. > Maybe I should have waited longer? > > Am I doing somthing terribly wrong. > > Brian >  > Brian > > > "Bob Phillips" wrote: > >> Public Sub ProcessData() >> Dim i As Long >> Dim iLastRow As Long >> >> With ActiveSheet >> >> iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row >> For i = 1 To iLastRow >> .Cells(i, "A").Value = Val(Left$(.Cells(i, "A").Value, 5)) >> Next i >> >> End With >> >> End Sub >> >> >> >>  >> HTH >> >> Bob >> >> (there's no email, no snail mail, but somewhere should be gmail in my >> addy) >> >> "bdehning" > wrote in message >> ... >> >I have a Zip Code Table with numbers with 9 digits or more showing up. >> > >> > How do I go about only allowing the field to show the first 5 numbers >> > and >> > dropping the rest after 5? >> >  >> > Brian >> >> >> 
#5




Limit Number in cell to 5 Digits
Actually I got two ways from some other sources.
1. First Rename the zip column and move it some where to the right. Replace this with a new blank column and give it the original title. Add a formula to the new column fields: =left(cell,5) This will give you the 5 left most characters. Using Right gives you the 5 most right characters. 2. The other way to make a new column and in the field use: =MID(A1,1,5), where A1 is cell with the number you want to limit. 1 means to start at the first number and 5 says to keep the first 5 numbers. Brian "bdehning" wrote: > Excuse me for being limited in knowledge about Macros. > > Do I need to change anything in the macro you provided to work in my > spreadsheet. > > I created the macro and saved it. I then highlighted the cells I wanted to > change and then ran the macro but it didn't appear to be doing what I wanted. > Maybe I should have waited longer? > > Am I doing somthing terribly wrong. > > Brian >  > Brian > > > "Bob Phillips" wrote: > > > Public Sub ProcessData() > > Dim i As Long > > Dim iLastRow As Long > > > > With ActiveSheet > > > > iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row > > For i = 1 To iLastRow > > .Cells(i, "A").Value = Val(Left$(.Cells(i, "A").Value, 5)) > > Next i > > > > End With > > > > End Sub > > > > > > > >  > > HTH > > > > Bob > > > > (there's no email, no snail mail, but somewhere should be gmail in my addy) > > > > "bdehning" > wrote in message > > ... > > >I have a Zip Code Table with numbers with 9 digits or more showing up. > > > > > > How do I go about only allowing the field to show the first 5 numbers and > > > dropping the rest after 5? > > >  > > > Brian > > > > > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
How to customize number to 10 digits including 2 digits after deci  Carina  Excel Worksheet Functions  3  September 20th 07 02:50 AM 
Count number of digits in a cell  Julieeeee  Excel Worksheet Functions  6  November 15th 06 05:55 PM 
Reducing the number of digits in a cell  Borge14  Excel Discussion (Misc queries)  6  August 10th 06 04:31 PM 
Limit Digits entered into a cell  Mike Smith NC  Excel Discussion (Misc queries)  1  July 7th 06 09:01 PM 
calculate using last four digits of number in cell  Andy Falkner  Excel Worksheet Functions  3  February 10th 06 06:30 PM 