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

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

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
>
>
>

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
>>
>>
>>

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
> >
> >
> >