Cell Formatting to disp. ### numbers

I am trying to format the cells so that it only allows three numbers t
be displayed.

To be more descriptive:

We work with zip codes quite often, but, we only use the first thre
digits.

Someone sent us a xls file with 12000 zip codes, in one column, and 
need to know how to make the column show only the first three digits o
all the zip codes..

there is another problem, when I convert them to a numeric value, i
removes the zero in front...ex. 08245, becomes 8245, but i need to kee
that zero in front.

Thanks

-Eri

--
Message posted from http://www.ExcelForum.com

0
8/31/2004 6:09:07 PM
excel 39879 articles. 2 followers. Follow

4 Replies
361 Views

Similar Articles

[PageSpeed] 59

Assuming your zip codes are in column A, you would put the following in
column B
=LEFT(A1,3)

"tusamni >" <<tusamni.1bv7h6@excelforum-nospam.com> wrote in message
news:tusamni.1bv7h6@excelforum-nospam.com...
> I am trying to format the cells so that it only allows three numbers to
> be displayed.
>
> To be more descriptive:
>
> We work with zip codes quite often, but, we only use the first three
> digits.
>
> Someone sent us a xls file with 12000 zip codes, in one column, and I
> need to know how to make the column show only the first three digits of
> all the zip codes..
>
> there is another problem, when I convert them to a numeric value, it
> removes the zero in front...ex. 08245, becomes 8245, but i need to keep
> that zero in front.
>
> Thanks
>
> -Erik
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nunayo (95)
8/31/2004 6:44:48 PM
Thanks!

I can make that work...

what about the removal of the first zero when I convert the zip code
to a numeric value?

Thanks again,

Eri

--
Message posted from http://www.ExcelForum.com

0
8/31/2004 7:06:51 PM
Format column B: format/cells/number/custom/000

You can do both at once by changing the formula to =value(left(a1,3))


"tusamni >" <<tusamni.1bva5d@excelforum-nospam.com> wrote in message
news:tusamni.1bva5d@excelforum-nospam.com...
> Thanks!
>
> I can make that work...
>
> what about the removal of the first zero when I convert the zip codes
> to a numeric value?
>
> Thanks again,
>
> Erik
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nunayo (95)
8/31/2004 7:52:10 PM
Are they 5 digit codes that are entered as numbers (not text)?

=left(text(a1,"00000"),3)

so 8245 (= 08245) would return 082.





"tusamni <" wrote:
> 
> I am trying to format the cells so that it only allows three numbers to
> be displayed.
> 
> To be more descriptive:
> 
> We work with zip codes quite often, but, we only use the first three
> digits.
> 
> Someone sent us a xls file with 12000 zip codes, in one column, and I
> need to know how to make the column show only the first three digits of
> all the zip codes..
> 
> there is another problem, when I convert them to a numeric value, it
> removes the zero in front...ex. 08245, becomes 8245, but i need to keep
> that zero in front.
> 
> Thanks
> 
> -Erik
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/31/2004 9:25:43 PM
Reply:

Similar Artilces:

There is any auto email format?
Hi All, I know some one can help me in this regard, that I am using excel for Insurance expiry date list more then 2000 list I have. Now what my question is everyday I can’t able to find every expiry date. I want some solution from that if there is any formula like if insurance date was expired how can I get the email for me? Like the date is 8/3/2010 and number is 123456 at the same day automatically I want email from for warning so that I can renewal the same. If there is any formula or something pls help me. I am very struggling for this. Have you tried Conditional ...

Conditional Formatting with Hyperlinks
OK, I have a hyperlink on tab 1 to a form on tab 2. The hyperlink is in red. What I want to do is get the the color of the hyperlink to change when just one word or a date has been placed in the form in tab 2 and not have the HL change because it's been clicked on. Pleaseeeeeeeee HELP!!!! ...

looking up cell address
I want to create a formulae that dynamicaly alters the row references of a SUM function nested within a INDIRECT function based on a start and end date variables. In order to to this I need a lookup that returns the row number and/or cell adress of the first cell that matches or exceeds the date variable and utilises the result in a SUM function nested within the the INDIRECT formulae (If possible I want to avoid inserting an additional column in the original data table that calculates the row refrence to be used with a simple vlookup) Any help offered would be appreciated ...

help choosing a chart format.
In my last job, we had a waterfall plot that would show time on the X axis, frequency on the y axes and the marker would change color according to magnitude of the signal. is there a good chart that will perform this type of plot? It is kind of like an XY scatter plot with a variable color. see peltiertech.com/Excel/Charts/Waterfall.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "fugazi48" <fugazi48@discussions.microsoft.com> wrote in message news:6A6088FA-C207-4CB2-A103-02CD9549D7E5@microsoft.com... > In my last job, we ...

Can you sort on the number of messages in thread?
Hi all, When you have your mail grouped on a field, is it then possible to sort on the number of messages in a thread. So threads with only 1 message will be on top en threads with more messages will appear lower. Hope this is possible..... All the best (this might be a solution to my previous question in another yet unanswered thread about filtering messages that are replied to) extra info: the version xp (but 2003 options are ok too) Best, Bert As far as I know, that's not an option "plurker" <nomail@nomail.com> wrote in message news:11ffe8fgqvmbc88@corp.su...

EXCEL chart legend formatting
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I've got it working in the excel data cells, but the formattin doesn't transfer to the legend and I can't directly change the legend text. I am having the same problem. Did you ever find a solution? Thanks! tauntonuse wrote: EXCEL chart legend formatting 28-Oct-09 Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I h...

Easy way to reference a specific cell in a new worksheet
This project is for week over week sales. A new worksheet is added every week. The current week's sales are in Column E for every salesperson and the previous week's sales are in Column F. I would like the Column F to automatically reference Column E on the previous worksheet every time a new worksheet is added by copying the previous worksheet. It's not that hard to work around it now, I would just like to save time and automate the process. Is this possible? Let me know if what I am attempting to do does not make sense. TIA Hi without VBA this is not possible -- ...

Multiple date validation in a single cell
I am trying to do a rather complicated validation (I think it i anyway!) I want to allow ONLY dates of a particular format to be entered int the one cell - i.e. YYYY OR mm/YYYY OR dd/mm/YYYY For example, the same cell can have either one of these format entered, but MUST match one of them to be accepted. 1970 is OK and is captured in the cell as 1970 (not 1/01/1970) 70 is assumed to mean 1970 on entry 05/1970 is OK, captured in the cell as 05/1970 (not 1/05/1970) 5/70 or May-70 is assumed to mean 05/0970 on entry 05/06/2004 is OK 5/6/04 or 5 June 04 is assumed to mean 05/06/2004 on entr...

Date format 03-03-08
How do I get a date to print as mm-dd-yy in a field? On Sun, 02 Mar 2008 23:43:02 -0500, BC wrote: > How do I get a date to print as mm-dd-yy in a field? Set the format property of the Date control to: mm-dd-yy -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...

How do I set up number eg 2000 to word two thousand in excel?
I need to convert numbers in excel to words Eg 2,200 Two thousand and two hundred dollars Here is the code to create the function: http://support.microsoft.com/?kbid=213360 "Lawrence" <Lawrence@discussions.microsoft.com> wrote in message news:82B59144-57C1-4C42-87B9-42D23D0858E5@microsoft.com... >I need to convert numbers in excel to words > > Eg 2,200 > > Two thousand and two hundred dollars Hi see: http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Regards Frank Kabel Frankfurt, Germany "Lawrence" <Lawrence@discussions.microsoft.com...

Subtracting two times in hh:mm:ss format
I need to subtract two times that are in hh:mm:ss format and they occur on different dates. Example: Time 1: 03/22/2005 19:09:47 Time 2: 03/23/2005 01:02:51 Result = Time 2 - Time 1 I've tried different things but I keep getting #VALUE! in the result cell. Any help would be appreciated. Thanks. Make sure the cells are correctly formatted as date and time. -- HTH RP (remove nothere from the email address if mailing direct) "A Khan" <A Khan@discussions.microsoft.com> wrote in message news:C179333B-FEDF-4542-8107-2F9B6AA6241F@microsoft.com... > I need to subtract...

Word Formatting
I need help with Word X. I have 2 documents that I am lately frequently trying to merge to a single PDF file. The files are a resume and a cover letter. I have the docs separate for most purposes, but occasionally want to create one file. Here's the problem.....when I try to cut/paste one into the other I loose most formatting (font size/spacing/colors) from the pasted document. I have tried all options in the "paste special" section to no avail..... Anyone have a workaround? Suggestion? MIKE T Well, to keep your formatting you would insert a section break before inser...

Concatenate Formating
I am running into a promblem when I use concatenate. Specifically, I am trying to bring together the following cells: A1 - Contains "N=" B1 - Contains "1,457" The Formula =CONCATENATE(A1,B1) Results in N=1457 Is there anyway I can retain the formatting of Cell B1. I need the result to include the comma (N=1,457) Thanks in advance Try using C1: =a1&b1 this should retain the values -- Message posted from http://www.ExcelForum.com Hi try =A1 & TEXT(B1,"0,0") -- Regards Frank Kabel Frankfurt, Germany Bob wrote: > I am running into a promblem ...

Check for data in cell
I want to check to be sure the cell selected in column C has data in it otherwise display an error message. Hi oldjay You can use the worksheet event shown below: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' Be sure to look at a single cell If Target.Cells.Count = 1 Then ' Check if a cell iu column C is selected If Target.Column = 3 Then ' Check id the selected cell is empty If IsEmpty(Target) Then MsgBox "On empty cell" End If End If End If End S...

how may I copy color of a other cell
Hello, I need a function that copy also the full format/color/size of a cell Example A1 = USD 12'500.00 (monetary size, 12 fat, blue bottom) A2 = mon 12.03.2004 (dates, 10 not fat, white bottom) If in A3 I type: =" A1 " I would like a blue bottom, 12 fat, etc. and If I type =" A2 " a white bottom, 10 not fat, etc.... Is it possible? Thank you for any help. Thierry, Maybe you can use the Format Painter. Select the cell that has the formatting you wish to copy, and click the Format Painter. It's a paintbrush on a button on the Standard toolbar. Now click the c...

Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the answer to this. Without much luck! I have finally come up with my own code and was hoping someone could have a look at it. I'm looking to clean it up and make it a little more efficient. Thanks in advance, Greg. Sub AddCommentsToChartPoints() Dim ws As Worksheet Dim ct As ChartObject Dim serSeries As SeriesCollection Dim ser As Series Dim Counter As Integer Dim ChartName As String Dim xVals As String Dim xAddress As String 'Loop through each worksheet in workbook For Each ws In Worksheets 'Lo...

Summing Every Nth Cell
I found a solution on the web for summing every Nth cell, but it is kind of convoluted and I ran into problems. Let's say I want to sum cells C4,C25,C46...etc all the way down. Basically every 21st cell. The problem might be that some of those rows are blank and others contain text. Is there an easy way to do this? Thanks One way: =SUMPRODUCT(--(MOD(ROW(C1:C999),21)=4),C1:C999) Personally, I think I'd use a helper column and put an indicator on every row I want summed. Say I put X in column D, then I could use: =sumif(d:d,"x",c:c) Then if I ever insert/delete rows,...

how do i add in numbers automatically based on adjacent cells cont
I'm trying to automatically enter data into multiple cells based on one cells data. For instance if a cell in column A = 1 then the adjacent cells in columns B, C and D will all have specific values which will be true for a value of 1 in column A. If the value in the cell in column A = 2 then the adjacent cells in columns B, C and D will have another set of specific values which will be true for a value of 2 in column A and so on. I think lookup tables is possibly the answer but the help isn't much good and I am struggling! In an out--the-way location, create a datalist tha...

Search invoice by number in MS CRM 3.0
Hello, I have a little Problem with the CRM Search. I want to search an Invoice as I use the "Look for" box on the Invoice Site. If I type a Invoice Number (for examle: INV-10001-ABCD1) in this Box there are no results because the CRM is searching only in the "Name" - Column. Is there any Solution to fix this Problem, so i can search by Invoice Number? The Problem is that if a customer phones us and say there are a mistake in the Invoice with the Number XYZ than we can=B4t find it just in time. I would be gratefull for any solutions. Best Regards Nillefiz In customiza...

create random NON-REPEATING numbers (e.g., 20 #'s between 1-100)
I want to create random numbers that do not repeat within a column. http://www.mcgimpsey.com/excel/udfs/randint.html Regards, Peo Sjoblom "Leon" wrote: > I want to create random numbers that do not repeat within a column. ...

Copy and Pasting Cells with VBA..help
I have a directory of business address to make into a database. The current format is like this: A B C R1 Co.Name R2 Add1 R3 CSZ R4 Phone R5 Blank R6 Blank R7 Co.Name (2) R8 Add1 (2) R9 CSZ(2) R10 Phone(2) R11 Blank R12 Blank and this repeats down through the spreadsheet. I need to keep the Co.Names in column A, copy/paste Add1 in B, CSZ in C and so on. Then all the data and blank cells in A must be deleted, except the company name of course. I was thinking of trying to loop this. Is this the best method? What w...

Can I add a text stop to a cell?
Is there anyway to place a text stop in a cell, so that text will no continue past the end of the cell? Text wrap isn't really what I' looking for, as I would like the cell to maintain its size. Also, a this cell is used for text, variations in the size of characters make limiting the number of characters in the cell not really work either. Any suggestions -- Message posted from http://www.ExcelForum.com PD, Try putting an almost hidden column (very narrow) to the immediate right of the column. Put something in the cells, like a single space character. -- Earl Kiosterud mvpearl...

COUNTIF Non Contiguous Cells Q
I want to create a formula that will Count the number of times cells is a range are greater than 1. Ordinarly I would use the formula =COUNTIF(F$10:F$168,">0"), however my range is non-contiguous, it is however relative, in that the cells I wish to Count are always 3 apart. So if I was to avoid having to long type a formula with all the ranges I want to count, how would I do it? Thanks Hi =SUMPRODUCT(--(MOD(ROW(A1:A15),3)=1),--(A1:A15>0)) Will count if row 1,4,7,10, 13 if >0 Hopes this helps. .... Per "Seanie" <seanryanie@yahoo.co.uk...

Conditional Formating #2
Hi, I want to format cells with specific colours. It appears that I'm limited to 3 format options. What I want to do is: if the cell contains: 1, S, SP, W, they are a green colour; SK, is another colour; and C, is yet another. I tried to put, if equal to : "1", "S", "SP", "W" to no avail... can I even do more than 3?. Regards, Kevin P.S. I tried to find the answer, maybe I'm not asking the question correctly? Thanks again. Hi Kevin, =OR(A1=1,A1="S",A1="SP", A1="W") Additional informatio...

Changing one phone format to another #2
Probably not too hard, but anyway I don't know how... : -- Krelle ----------------------------------------------------------------------- Kreller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1163 View this thread: http://www.excelforum.com/showthread.php?threadid=26412 Can you give us the "from" and "to" formats? >-----Original Message----- > >Probably not too hard, but anyway I don't know how... :( > > >-- >Kreller >--------------------------------------------------------- --------------- >Kreller...