A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Limit Number in cell to 5 Digits



 
 
Thread Tools Display Modes
  #1  
Old December 19th 07, 07:48 AM posted to microsoft.public.excel.misc
bdehning
external usenet poster
 
Posts: 11
Default 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  
Old December 19th 07, 09:34 AM posted to microsoft.public.excel.misc
Bob Phillips
external usenet poster
 
Posts: 10,594
Default 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  
Old December 19th 07, 04:38 PM posted to microsoft.public.excel.misc
bdehning
external usenet poster
 
Posts: 11
Default 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  
Old December 19th 07, 06:31 PM posted to microsoft.public.excel.misc
Bob Phillips
external usenet poster
 
Posts: 10,594
Default 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  
Old December 19th 07, 06:48 PM posted to microsoft.public.excel.misc
bdehning
external usenet poster
 
Posts: 11
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 04: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 05:30 PM


All times are GMT +1. The time now is 06:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.