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

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

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

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

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