How do I format cells to a specific number of digits?

I am trying to format a column so I can enter 5 digits only. It has between 5 
and 7 digit numbers in it right now and I need to delete the remaining ones. 
It is about 350 numbers, so it would be a lot of work to go into each cell 
and delete the remaning numbers. Is therea way I can format the whole column 
to allow 5 digits? The same happened with letters. I had codes in it that 
consisted of numbers and letters and the client only wants the first two 
letters to remain. How can it be formatted so only 2 characters are allowed 
to keep me from having to enter each cell separately?
0
Gabriele (5)
2/5/2005 3:03:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
427 Views

Similar Articles

[PageSpeed] 28

Hi

formatting won't solve your problem - but i can be done using a "helper" 
column
say your numbers are in column A in  B1 type
=left(A1,5)
move the mouse over the bottom right hand corner of cell B1 and your mouse 
should change to a + now double click and the formula will be filled down 
for you.  Click on the letter B to select the column and choose copy, click 
on cell A1 and choose edit / paste special - values - click OK and now 
delete column B

Cheers
JulieD

"Gabriele" <Gabriele@discussions.microsoft.com> wrote in message 
news:F10AAE62-5620-4C84-BF4F-3E9C1EB8AE5F@microsoft.com...
>I am trying to format a column so I can enter 5 digits only. It has between 
>5
> and 7 digit numbers in it right now and I need to delete the remaining 
> ones.
> It is about 350 numbers, so it would be a lot of work to go into each cell
> and delete the remaning numbers. Is therea way I can format the whole 
> column
> to allow 5 digits? The same happened with letters. I had codes in it that
> consisted of numbers and letters and the client only wants the first two
> letters to remain. How can it be formatted so only 2 characters are 
> allowed
> to keep me from having to enter each cell separately? 


0
JulieD1 (2295)
2/5/2005 3:08:05 PM
oh, i forgot to add - once you've changed all the values to 5 characters - 
you can then limit what can be entered into the cell using data / 
validation - choose the column, choose data / validation / allow - whole 
numbers, less than 100000
this will then stop numbers with 7 digit from begin entered.

Cheers
JulieD


"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message 
news:uDm1VS5CFHA.624@TK2MSFTNGP15.phx.gbl...
> Hi
>
> formatting won't solve your problem - but i can be done using a "helper" 
> column
> say your numbers are in column A in  B1 type
> =left(A1,5)
> move the mouse over the bottom right hand corner of cell B1 and your mouse 
> should change to a + now double click and the formula will be filled down 
> for you.  Click on the letter B to select the column and choose copy, 
> click on cell A1 and choose edit / paste special - values - click OK and 
> now delete column B
>
> Cheers
> JulieD
>
> "Gabriele" <Gabriele@discussions.microsoft.com> wrote in message 
> news:F10AAE62-5620-4C84-BF4F-3E9C1EB8AE5F@microsoft.com...
>>I am trying to format a column so I can enter 5 digits only. It has 
>>between 5
>> and 7 digit numbers in it right now and I need to delete the remaining 
>> ones.
>> It is about 350 numbers, so it would be a lot of work to go into each 
>> cell
>> and delete the remaning numbers. Is therea way I can format the whole 
>> column
>> to allow 5 digits? The same happened with letters. I had codes in it that
>> consisted of numbers and letters and the client only wants the first two
>> letters to remain. How can it be formatted so only 2 characters are 
>> allowed
>> to keep me from having to enter each cell separately?
>
> 


0
JulieD1 (2295)
2/5/2005 3:15:37 PM
Gabriele,

For the numbers already in the column, in a helper column:

=left(A2, 5)         or
=right(A2, 5)

Or for the case where you want only the first two characters:

=left(A2, 2).

Copy down with the Fill Handle.  Now to permanently remove the original 
stuff, copy the helper column, then directly over the original stuff, Edit - 
Paste special - Values.  Now you don't need the helper column any more.

Your post hinted at preventing entering more than five characters.  For 
that, use Data - Validation, and select Text Length and "equal to." .  Set 
it to 5.  Or something similar
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Gabriele" <Gabriele@discussions.microsoft.com> wrote in message 
news:F10AAE62-5620-4C84-BF4F-3E9C1EB8AE5F@microsoft.com...
>I am trying to format a column so I can enter 5 digits only. It has between 
>5
> and 7 digit numbers in it right now and I need to delete the remaining 
> ones.
> It is about 350 numbers, so it would be a lot of work to go into each cell
> and delete the remaning numbers. Is therea way I can format the whole 
> column
> to allow 5 digits? The same happened with letters. I had codes in it that
> consisted of numbers and letters and the client only wants the first two
> letters to remain. How can it be formatted so only 2 characters are 
> allowed
> to keep me from having to enter each cell separately? 


0
nothanks4548 (968)
2/5/2005 3:17:26 PM
Reply:

Similar Artilces:

adding digits to front/end of fax numbers
how do i add "1" before each fax number and ",,7741" at the end of each one? we have to dial one to dial long distance and we have a code to use the long distance in our office. please help quickly. Thanks Hi! Fax number in A1: =1&A1&",,7741" If you want a space after the 1: =1&" "&a1&",,7741" Biff "Luke" <Luke@discussions.microsoft.com> wrote in message news:D1C25AF0-7FBC-43CE-855D-FCCA77CD9759@microsoft.com... > how do i add "1" before each fax number and ",,7741" at the en...

Formatting a variable
I have the following code: Type ProdMatrix APN As Single ..... Dim ThisProd As ProdMatrix ..... tmpThisProd.APN = .Cells(tmpSrcRow, 2) The variable is supposed to contain the number 9317382120228, but instead contains 9.317382E+12 Then, when I execute the following code: With ThisProd Cells(LastRowDest, 1) = .APN & .StoreNo I get the result of 9.317382E+123201 in the cell where: ThisProd.APN = 9.317382E+12 ThisProd.StoreNo = 3201 I was expecting to get the number 93173821202283201. Why is the value of APN taken literally and not by...

Printing or formatting issue?
Dear all, Please bear with me on this one, I have posted something similar in the past and thought this issue had been resolved but hasn't!! In Finance we have staff who work on individual worksheets, these are then sent to one person who collates them into one workbook; this is then sent to our Finance MD and PA. The person who sends the workbook can print all sheets out fine on his HP5000n (A4 and A3), however the Finance PA (HP4050) and the MD (HPxxx) cannot, they complain that the formatting is all "off". I can confirm that all these users and those who originally created t...

How do I zip sort mailing list when I have 5 digit AND 9 digit zip
I have a mailing list with zips in XXXXX and XXXXX-XXXX all mixed in. When I sort them, it seems to put all the 5 digits on the top half and 9 digits on the bottom half. I have made sure the column settings are set to special/zipcode. I have also tried setting them as Zip Code + 4 but it converts all the XXXXX versions to 0000X-XXXX. What am I doing wrong? Convert to TEXT -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Greg" <Greg@discussions.microsoft.com> wrote in message news:97AB96D9-EAE6-4E6F-8AA1-1DE4137EDA06@microsoft.c...

conditional formating 04-28-10
I have the following formula for column D and E =D2<Today() red =(D2-Today())<30 green I need to add a formula that if a date is in column c then column D and E are black. What are you formatting? The font color or the cell fill color? -- Biff Microsoft Excel MVP "Cheryl" <Cheryl@discussions.microsoft.com> wrote in message news:6C0EA3C7-7A01-42EB-9AF0-0B8E7DC99915@microsoft.com... >I have the following formula for column D and E > =D2<Today() red > =(D2-Today())<30 green > I need to add a formula that if a date is in column c th...

filtering account numbers from a worksheet
I'm new to excel. I have created a spreadsheet containing my newspaper's subscriber list, containing account numbers and addresses. I want to merge another list of subscribers(with account numbers and addresses) with the first list to scrub those addresses, etc. from that list. Can anyone advise me? Hi not completely sure what you're trying to do. But as a starting point: http://www.cpearson.com/excel/duplicat.htm#InOneNotOther http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon -- Regards Frank Kabel Frankfurt, Germany fouraker wrote: > I'm new to excel. I...

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Cell shading not working in Excel 2003
Anyone run into this bizarre behiour ?... When editing spreadsheet using Excel 2003 (Student Teacher Ed.)in normal view mode, the cell shading (and patterns) do not display on the cell. Black and white patterns do not show and colors do not show. The cell remains default white background. The shading patterns and colors selected **do** however, display perfectly when view sheet in print preview mode..I'm not using conditional cell formatting or anything that would otherwise affect the cell shading. Running on Windows XP Home. marty Hi Marty, Sound like a video problem. I can add border, f...

Copying Border Formatting
Hi folks, I have a large spreadsheet which is essentially a diary. I want to format the days and weeks, with thin and thick borders to distinguish between them. The spreadsheet already has lots of varied fill formatting for meetings that are held. I can't see a way of either format painting, or paste special-ing, just borders. Is it possible? Thanks -- StainlessSteelRat http://www.stainlesssteelrat.net Stanley: It's not gonna end like this. Gabriel: Oh, come on, Stan. Not everything ends the way you think it should. Besides, audiences love happy endings. True enough that...

typing text onto two lines (one above the other) in one cell
What is the command and syntax to do the above? Thank you. Will, Press the Alt-Enter keys at the same time in a sentence to go to a different line within the same cell. ---- Regards, John Mansfield http://www.pdbook.com "Will Naylor" wrote: > What is the command and syntax to do the above? > > Thank you. > ...

Excel Regional Date Format Options
A client of ours in NZ is complaining that date format options for English (New Zealand) have changed from older versions of excel (they are using 2003) Some of their spreadsheets have dates formatted as dd-mmm-yy, mmm-yy and dddd,dd,mmm but these options do not exist anymore. Is there anyway to add options to this list without using the custom format option? Thanks, Jesse I just compared the Excel 97 and Excel 2003 built-in date formats and they are mostly unchanged. 2003 has a few more but I don't think there were any subtractions. The formats dd-mmm-yy and mmm-yy are righ...

Format>Row>Autofit
Hi in Excel 2003, I have a 380row x 2col sheet, all text, no functions so far. Text was imported from Word, other Excels etc. Up to 10 lines of text in some cells, word-wrapped, many new-line (Alt + Enter) breaks. No merged cells. Problem is, Format>Row>Autofit is unreliable. For 90% of cells works fine but some either show white space between text lines, or shrink too much, cutting off last text line(s). If adjusted manually, seems unstable - will revert for no reason. Looks like you need to remove the formatting before you pasted into the new spreadsheet. when youu paste - if you ...

Changing a customers Account Number?
Is it safe to edit a customers account number? I have some irregularities i would like to sort out, and would rather maintain the customers purchase history than make a new account? If i directly change the account number - will i mess anything up? In my personal opinion if the account number was system generated, I'd leave it alone; if you created your own account numbers for your customers, I don't think you'll have any issues, sql being a relational db and all.....nevertheless, back up your db and recreate it on say your laptop....then change one customer account numb...

How do you change the size of a range of cells in a column/row
without changing all cell sizes in those rows or columns You can't, width and height are properties of columns and rows, not cells. You can merge cells in two columns or rows to combine their width or height. In article <0F00CD03-E47C-44AD-9185-89BEEF4D3FEB@microsoft.com>, SOkoll <SOkoll@discussions.microsoft.com> wrote: > without changing all cell sizes in those rows or columns ...

Save document in 9 slide format view
How do I save the document in the 9 slide format view vs. the individual panes? I would suggest printing the document to pdf. select handouts and set to 9 slides per page. hope that helps -- Emma Bannister PowerPoint Specialist www.presentationstudio.com "D @ rrcsb" <D @ rrcsb@discussions.microsoft.com> wrote in message news:7582D4E9-7BA8-417F-81AE-9603F4D00B87@microsoft.com... > How do I save the document in the 9 slide format view vs. the individual > panes? ...

summing cells of Time
I got an answere to my other message but the trouble is it did not work -- --when summing a group of cells that have time in then the help says use this formula =Sum (cell:cell) if it is to be more than 24 hours the formula is =sum(cell:cell)*24 the trouble is nomater how I format the cells it still comes up either 0 or 0:00 Hi format your target cell with the custom format [hh]:mm to prevent the rollover after 24 hours -- Regards Frank Kabel Frankfurt, Germany Don Scarbrough wrote: > I got an answere to my other message but the trouble is it > did not work -- --when summing a ...

Number Stored As Text 11-18-09
What property of a cell should I check to determine whether that cell contains a number stored as text? I have some cells which Excel 2007 tells me, via the office assistant, are numbers stored as text. I want to loop through all the cells in the used range testing for and correcting this. Paul Smith On Nov 18, 12:11=A0pm, "Paul W Smith" <p...@NOSPAM.twelve.me.uk> wrote: > What property of a cell should I check to determine whether that cell > contains a number stored as text? > > I have some cells which Excel 2007 tells me, via the office as...

Need formula to cal percentage of 2 numbers
Hi, I would like the formula that will compare two numbers and give me the percentage in the third column. ex C4 compared to B4 put in cloumn D4 as a percentage, New to excel and need to know how to put the formula in the cell too? thanks for hel p in advance -- Mike B =C4/B4 and format as percentage -- HTH Bob Phillips "mike b" <mikeb@discussions.microsoft.com> wrote in message news:5D45EB6A-F7D6-4B53-B3DC-EEA724E69298@microsoft.com... > Hi, > > I would like the formula that will compare two numbers and give me the > percentage in the third column. > ...

Button to go to a specific record in a form
Hello, I have two forms. I need to use a command button to go to a second form's specific record. a. In the wizard, when it is time to chose the fields to match, there is nothing in the left side of the screen. b. How do I do this in code. Thnaks for all of your help. Joe In the Click event of the command button, use the primary key field of the record you want to open in the second form in the OpenForm Where argument. This is a good example in VBA Help. -- Dave Hargis, Microsoft Access MVP "Joe C" wrote: > Hello, > > I have two forms. I need to use a c...

copy a cell value not its function
Excel question: ce11 A1 value=500 Cell B1 value=600 Cell C1 value=2000 Cell D1 value=50 Cell E1 value=C1-D1-B1-A1 use contrl+D to copy cell E1 value all the column down to cell E100 What I wnat is to use contrl+D to copy E1 value not its fuction. Please if someone could help. Hi KC, To copy down the snapshot value of E1: Select E1 | Copy | Select E1:E100 | Paste Special | Values | Ok -- OR -- To copy down a value which wll equal E1 and reflect any change in E1: Select E1 | Select the entire formula in the formula bar and hit F4 to convert to an absolute formula | drag down --- Reg...

conditional format rage based on entries within range
I have a spreadsheet in excel 2003 where a range of cells contain different text. If there is no text in any of the cells within the range I would like the range conditionally formated (red), however if any or all of the cells within the range contain text then no format should be applied;e.g. If A1 to D1 are blank format cells red, when text is entered into B1 remove formatting. All help greatly recieved. Simon. Simon, Select A1 to d1 and then format|Conditional formatting - Select 'formula is' enter the formula =A1="" Pick a colour OK -- Mike...

using row number or column letters in formula
Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"...

date format frustration!
I'm having no luck! When I enter 07/29/2004 it comes out 36735. I've tried the format option but no luck...what am I missing? Thanks in advance! Hi formating this cell as 'Date' should do -- Regards Frank Kabel Frankfurt, Germany "ogawak" <ogawak@discussions.microsoft.com> schrieb im Newsbeitrag news:32299F17-318F-4770-B08E-9032FDE63AE5@microsoft.com... > I'm having no luck! When I enter 07/29/2004 it comes out 36735. I've tried > the format option but no luck...what am I missing? > > Thanks in advance! Check your Date settings at T...

Dealing with Office 2007 & Office 2003 file formats
My company plans on moving to Office 2007 soon. However, we have a delimma with how to handle how Office files are saved. For example, if a person open a .doc & makes changes in Office 2007 and some of those changes are not supported by the .doc, then the file is saved as a ..docx. This leaves us with two files... a doc & a docx. We really don't want someone updating the .doc anymore. Any suggestions on how to deal with this? Side notes: ~We have the compatibility pack installed on all computers. ~The batch converter tool doesn't seem like a good fit for...

skipping blank cells in dynamic chart
I am creating a dynamic chart that captures 24 months of data. I'm using a vlookup to put data into the cells and it goes out 5 years. It looks like the dynamic chart code picks up the vlookup code 5 years out and tries to chart it. It also picks up months where no production accured. Is there away to ignore the vlookup if no data is in the cell? Here is a sample of my data: Dec 05 700 Jan 06 725 Feb 06 720 Mar 06 695 Apr 06 710 "blank" "blank" Jun 06 720 Jul 06 700 "blank" "blank" Aug 06 ...