Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of digits in a cell | Excel Worksheet Functions | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions | |||
Reducing the number of digits in a cell | Excel Discussion (Misc queries) | |||
Limit Digits entered into a cell | Excel Discussion (Misc queries) | |||
calculate using last four digits of number in cell | Excel Worksheet Functions |