dang cell format

I'm trying to set an Excel cell format  like I can do in Access to prevent
input errors.
Cell input is 1234-123-1234-123, that's to say, non-sequential numbering but
using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 numbers.
If the entry is wrong, go to halt/stop.

What hasn't worked;
1. conditional format
2. Format, cells, 'custom'
          I've tried ####-###-####-### under custom but it fails because it
allows more then 4 numbers at the very start.
          I "can" get,    123456-789-1234-234 The last 3 sequences work but
not the 1st sequence.
          Format set as ####-###-####-### , then input 1234-123-1234-123
states format is wrong whether I input dashes
          or not.

3. (####-###-####-###)
4. '####-###-####-###'
5. "####-###-####-###"
6. =exact(####-###-####-###)
7. =exact"####-###-####-###"
8. =exact####-###-####-###
9.  =exact, ####-###-####-###
10. (####)-(###)-(####)-(###)

Any help here would be greatly appreciated.........Dan


0
Deckert
4/21/2004 8:31:59 PM
excel 39879 articles. 2 followers. Follow

4 Replies
808 Views

Similar Articles

[PageSpeed] 28

Hi Deckert,

Try the following formula in Conditional Formatting. I checks the length =
of the string and that the dashes are in the right places. It does not =
check for digits/letters, though.

=3D((MID(A1,5,1)=3D"-")*(MID(A1,9,1)=3D"-")*(MID(A1,14,1)=3D"-")*(LEN(A1)=
=3D17))=3D1

HTH
Anders Silven


"Deckert" <deckert at owt.com> skrev i meddelandet =
news:4086da44_2@127.0.0.1...
> I'm trying to set an Excel cell format  like I can do in Access to =
prevent
> input errors.
> Cell input is 1234-123-1234-123, that's to say, non-sequential =
numbering but
> using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 =
numbers.
> If the entry is wrong, go to halt/stop.
>=20
> What hasn't worked;
> 1. conditional format
> 2. Format, cells, 'custom'
>           I've tried ####-###-####-### under custom but it fails =
because it
> allows more then 4 numbers at the very start.
>           I "can" get,    123456-789-1234-234 The last 3 sequences =
work but
> not the 1st sequence.
>           Format set as ####-###-####-### , then input =
1234-123-1234-123
> states format is wrong whether I input dashes
>           or not.
>=20
> 3. (####-###-####-###)
> 4. '####-###-####-###'
> 5. "####-###-####-###"
> 6. =3Dexact(####-###-####-###)
> 7. =3Dexact"####-###-####-###"
> 8. =3Dexact####-###-####-###
> 9.  =3Dexact, ####-###-####-###
> 10. (####)-(###)-(####)-(###)
>=20
> Any help here would be greatly appreciated.........Dan
>=20
> 
0
4/21/2004 9:03:13 PM
CORRECTION!

Conditional Formatting in my previous post should be Data Validation.

Anders Silven

"Anders S" <anders_silvenblaha@hotmailxyz.com> skrev i meddelandet =
news:OcsQaQ%23JEHA.628@TK2MSFTNGP11.phx.gbl...
Hi Deckert,

Try the following formula in Conditional Formatting. I checks the length =
of the string and that the dashes are in the right places. It does not =
check for digits/letters, though.

=3D((MID(A1,5,1)=3D"-")*(MID(A1,9,1)=3D"-")*(MID(A1,14,1)=3D"-")*(LEN(A1)=
=3D17))=3D1

HTH
Anders Silven


"Deckert" <deckert at owt.com> skrev i meddelandet =
news:4086da44_2@127.0.0.1...
> I'm trying to set an Excel cell format  like I can do in Access to =
prevent
> input errors.
> Cell input is 1234-123-1234-123, that's to say, non-sequential =
numbering but
> using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 =
numbers.
> If the entry is wrong, go to halt/stop.
>=20
> What hasn't worked;
> 1. conditional format
> 2. Format, cells, 'custom'
>           I've tried ####-###-####-### under custom but it fails =
because it
> allows more then 4 numbers at the very start.
>           I "can" get,    123456-789-1234-234 The last 3 sequences =
work but
> not the 1st sequence.
>           Format set as ####-###-####-### , then input =
1234-123-1234-123
> states format is wrong whether I input dashes
>           or not.
>=20
> 3. (####-###-####-###)
> 4. '####-###-####-###'
> 5. "####-###-####-###"
> 6. =3Dexact(####-###-####-###)
> 7. =3Dexact"####-###-####-###"
> 8. =3Dexact####-###-####-###
> 9.  =3Dexact, ####-###-####-###
> 10. (####)-(###)-(####)-(###)
>=20
> Any help here would be greatly appreciated.........Dan
>=20
>
0
4/21/2004 9:10:59 PM
Deckert,

I think Anders solution would work well with Data Validation.  I've changed
his expression slightly. Select the cells, then Data - Validation - Custom.
You should be able to paste it right from here:

=AND((MID(A1,5,1)="-"),(MID(A1,9,1)="-"),(MID(A1,14,1)="-"),(LEN(A1)=17))

For this to work, A1 must be the active (white) cell in your selection when
you do Data - Validation.  With Data Validation, you can supply an
appropriate user-friendly error message, and a prompt that appears when the
cell has been selected.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Deckert" <deckert at owt.com> wrote in message news:4086da44_2@127.0.0.1...
> I'm trying to set an Excel cell format  like I can do in Access to prevent
> input errors.
> Cell input is 1234-123-1234-123, that's to say, non-sequential numbering
but
> using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 numbers.
> If the entry is wrong, go to halt/stop.
>
> What hasn't worked;
> 1. conditional format
> 2. Format, cells, 'custom'
>           I've tried ####-###-####-### under custom but it fails because
it
> allows more then 4 numbers at the very start.
>           I "can" get,    123456-789-1234-234 The last 3 sequences work
but
> not the 1st sequence.
>           Format set as ####-###-####-### , then input 1234-123-1234-123
> states format is wrong whether I input dashes
>           or not.
>
> 3. (####-###-####-###)
> 4. '####-###-####-###'
> 5. "####-###-####-###"
> 6. =exact(####-###-####-###)
> 7. =exact"####-###-####-###"
> 8. =exact####-###-####-###
> 9.  =exact, ####-###-####-###
> 10. (####)-(###)-(####)-(###)
>
> Any help here would be greatly appreciated.........Dan
>
>


0
nowhere1083 (630)
4/21/2004 9:17:04 PM
I whole-heartedly thank you both for your reply & assistance.
I was unable to get Anders to work as needed but Earls' did perform as
needed.

As you noted, it does not allow for verification of #'s vs. text but that
won't be a problem. Thank you both again!

Dan




-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 100,000 Newsgroups - 19 Different Servers! =-----
0
deckert (3)
4/23/2004 5:01:34 PM
Reply:

Similar Artilces:

cell in cell
Okay, I got the money cell in cell formula to work, but now I want to do one with a date, and can't seem to get the date to show up correctly. If I just enter the formula in the text string as &(A1) & where A1 is the date January 7, 2005, I get the numeric value of the date 38359. How do I switch the format in the calculation? Is there a way to bold is within the rest of the sentence? (This is Excel 97...I'll be getting an upgrade to Xp and 2003 in a week...) This will convert the 38359 to a date format in your formula: =TEXT(A1,"mm/dd/yy")&" your c...

Automate unprotecting of blank cells
I have a spreadsheet that has several cells with values entered int it. I want others to add some values, but I don't want them to change cell that already have values. I know that I can use goto, special, constants and protect thos cells. Can someone recommend a macro or method to review each cell within specified range and unprotect it if it is blank? Any suggestions would be appreciated. Jim Palme -- Jim Palme ----------------------------------------------------------------------- Jim Palmer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=521 View t...

Unrecognized database format.
Hi, I've recovered data from damaged partition and then it was saved. But after I open the file, it can't open and show the popup menu "Unrecognized database format". The size of file is 3,460 kb. How can I do for recovery that file. Any of suggestion. Thanks, c.sumeth Is the data in a table(s). Try creating a new DB and then use the import data facility from the old into the new. (File - Get External Data) It's worth a try before you start on any more complex. -- Wayne Manchester, England. "Kim" wrote: > Hi, > ...

IF in cells
Is there a maximum amout of multiple IFs you can have in one cell? I have a formula that should work but says there is an error, cut it down it works. I have 24 ifs in the cell..... Cheers, Craig... Craig It's seven in versions up to 2007, 2007 has more. Consider a data table and using VLOOKUP will normally work better -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "Craig Coope" <coope1999@hotmail.com> wrote in message news:56ktrdF292fsqU1@mid.individual.net... > Is there a maximum am...

Find cells with conditional formats
Is there an easier way to find the cells with conditional formats than going to each cell and selecting format, conditional formatting? On Aug 28, 9:37=A0pm, mjones <mich...@quality-computing.com> wrote: > Is there an easier way to find the cells with conditional formats than > going to each cell and selecting format, conditional formatting? Found it. It's under Edit, Go To, Special. Thanks. My free "Formats & Styles" Excel add-in provides a count of CF cells that includes a count of cells with multiple conditions. Download from... http://excelusergroup.org/m...

Inporting data to cell that exceeds the size of the cell.
When I import data to the excel-document that exceed the size of the cell, the cell will expand vertical. I want the text to be horizontal. How can I do that? I use text-boxes for import. When I use the fuction display "full screen" in some of my excel documents, I dont see the full screen. It is problably someting in the setup, but what Can somebody help please? SOSK, Prior to the import, set the column width of the cell that will be receiving the data to be as wide as you want it to be after the import. That will allow Excel to spread the data out rather than expanding the row ...

Replacing "*" in a cell
Hi all, Got a quick question, I downloaded my bank statement in txt format. When there is a skip i check number, the bank mark the next check number using "*". Example chk 1011 1013*. I want to get rid of the "*" using 'Replace' but Excel consider it a 'all' so excel replaces all of the value. All I want to do is to change '1013*' to just '1013' without * so I ca sort it by check number. Anyone? Thank you -- Message posted from http://www.ExcelForum.com Hi tinop Use this instead ~* -- Regards Ron de Bruin http://www.rondebruin.nl ...

Returning the formula in a cell
Hi I have umpteen sheets in my excel, so I think it would be very useful if I could have hyperlinks to each of these sheets on the summary page. Let us say I have, in Sheet 1, Cell A1 the formula "=Sheet2!A2". Lets also say that Cell Sheet2!A2 contains "Hello" In colum 2 (B1), i want to be able to use the HYPERLINK formula - if I click on this cell, it shud take me to Sheet 2 I want to pick up the text "Sheet2!A2" from A1 and not the value returned by "=Sheet2!A2"(which is "Hello"). Which formula would I use to pick up the formula in cel...

Stop excel emedding (opeing in mail format) when outlook is open.
When I have outlook open and I then try and open a excel file it opens in a mail format and try as I have I can't find the setting to stop it from doing so. Please help because it is really frustrating and laborious having to close outlook just to open a excel file normally. Does mail format mean that you see the address boxes at the top? If it does, maybe just toggling this off would be easier. File|Send to|Mail Recipient (that icon in front of "mail recipient" is difficult to decipher, but it can be toggled up or toggled down.) Help Hey wrote: > > When I have out...

How do I display summary of different cells in one cell?
Hi, I am trying to summarize in one cell, all the different shoe sizes worn through the week. my spreadsheet looks like this Mon Tue Wed Thu Fri Shoe size worn 5 6 4 8 9 Shoe Sizes Worn through week 5,6,4,8,9 I'd like to somehow display 5,6,4,8,9 in one cell. Is there an easy way to do this without a huge nested IF thing? Thank you in advance -Tom tomlee4now@gmail.com wrote: > I'd like to somehow display 5,6,4,8,9 in one cell. Is there an easy way > to do this without a huge nested IF thing? you can use st...

Cell matching issue!
Hi guys, Ok here is my issue: I have a workbook containing 2 sheets. One sheet contains a list of ETFs with livefeed data from Bloomberg about two different volume measures.I then use this data to do a simple calculation for the performance of each ETF. The issue is the fact that in the other sheet I want a table with the top 5 performers and the worst 5 performers. What I did in order to receive the names of the ETFs, provider, performance etc, I used the VLOOKUP function. The performance is the column on the far left. =VLOOKUP(LARGE(data!$C$4:$C$41,1),data!$C$4:$L$41,7,TRUE) data is the...

How to sort cells by dates when some cells are links to other cell
When try to sort by date column, with some cells having dates created by reference/link to anther cell not in the same column, the sorted cell/s changes to a reference error (guess thats what Id call it, not sure what the official name is) =#REF! Any idea how to sort referenced date cells without getting that error ? Thank you for your help. akm Hi, Make the date reference absolute: =$A$10 Cheers, Shane Devenshire Microsoft Excel MVP "akm" <akm@discussions.microsoft.com> wrote in message news:448236ED-7E88-4279-AABC-E509806F945F@microsoft.com... > When try to sort ...

custom format changes to special
I need to regularly reformat numbers in cells into a custom number (000000000). I create a formatted cell and place a number 1 in it (000000001). I thne copy it and use paste special multiply. This has work great reformatting cells until this week. I tried it and now the cell formats itself into Special Social Security (Portuguese) format. It changes all of the cells even the orginal. Any ideas? I can not change it back. I need the formatting to help with vlookups Thanks Jan ...

please help: find nonblank cell in group of cells...
Hello all, Please help. How can I find nonblank cell in group of cells (one row) and than get value from cell in that column and specified row and return in another cell. And repeat that in many (about 300 rows)? Thank you spanic +-------------------------------------------------------------------+ |Filename: book1.jpg | |Download: http://www.excelforum.com/attachment.php?postid=5162 | +-------------------------------------------------------------------+ -- spanic ------------------------------------------------------------------------...

Cell formatting protection
Here is my delima. I have excel 2002 and i have a sheet that i do no want anyone editing without entering in a password to unprotect it. Currently i have every cell on the sheet locked. and I wanted to setu some advanced permissions so that certain users dont even have t unlock the file that part works great. This sheet background color are used to indicate if the data has been processed or not ( not m idea to do it that way ) so i want to make it so that if some rando person that is not specified in my permissions list tries to change th background color it wont let him without typing in a...

find cell value
hi i have this formula that identifies the last column with data in it. =ADDRESS(35,MATCH(6.022*10^23,33:33)) this works a treat what i would like to do is us the result of this and subtract 3 column of it ie ADDRESS(35,MATCH(6.022*10^23,33:33)) = N35 i would like to have a formula that takes N35 and sub tracts 3 columns fro it giving K33 i then intend making that my range ie k12:n33 and copy the data else where to work on thanks kevin If =ADDRESS(35,MATCH(6.022*10^23,33:33)) returns $N$35 then =ADDRESS(35,MATCH(6.022*10^23,33:33)-3) will return $K$35...

selecting cells #3
When working with a large group of cells, I find it easier to click the top cell, scroll to the bottom, and click the last cell while holding the shift key down. The problem is, when I want to select a second column of data, I have to select the top cell using the control key, the pull down the cursor manually. Is there a way to select 2 or more non-adjacent columns without having to scroll down using the mouse? Nevermind - figured it out myself. The easiest things are often so obvious they are hard to see. "Wazooli" wrote: > When working with a large group of cells, I...

dang cell format
I'm trying to set an Excel cell format like I can do in Access to prevent input errors. Cell input is 1234-123-1234-123, that's to say, non-sequential numbering but using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 numbers. If the entry is wrong, go to halt/stop. What hasn't worked; 1. conditional format 2. Format, cells, 'custom' I've tried ####-###-####-### under custom but it fails because it allows more then 4 numbers at the very start. I "can" get, 123456-789-1234-234 The last 3 sequences work but not the 1st seque...

Merging cells in a shared workbook......
Excel 2003 I ran into something I haven't as of yet and am wondering if it is due to working within a shared workbook. I am unable to merge cells as the option to do so is greyed out. The sheet is not protected by the protect command. Forgive me if this has been asked but since I access this from work, I have to use the web browser and not an actual reader and I haven't found the search function (if there is one). Thanks for any and all replies. :) It is due to being shared. >-----Original Message----- >Excel 2003 > >I ran into something I haven't as of yet ...

Formatting cells with numbers and text
I have a column of numbers with text; i.e., 23L252350, 06K100997, 50J304888, etc. These numbers and text need to have a hyphen inserted after the first two numbers/characters and the next four numbers/characters, i.e., 23-L252-350, 06-K100-997, 50-J304-888. I tried using Format-Cells-Number-Custom, but cannot seem to get it to work since the 3rd character is usually (but not always) text. Any assistance would be greatly appreciated. Thank you. Hi, try =LEFT(B7,2)&"-"&MID(B7,3,4)&"-"&RIGHT(B7,3) change the cell # to fit your needs "Dazed and...

Outlook XP. incoming mail now only in text format
Something happened yesterday that had all of my incoming mail appear in text format rather than the HTML (layed out with photos and art included as it isued to). I cannot think of anything that I may have done to affect this. Does anyone know of a setting change that may correct this? Many thanks, Patrick ...

How do I select a cell 5 cells from the active cell?
seems simple enough, but i can't figure it out. i am at a particular random location, and I need to select the cell 5 rows over. TIA JasonK One way: Public Sub SelectCell5RowsFromActiveCell() With ActiveCell If .Row <= Rows.Count - 5 Then _ .Offset(5, 0).Select End With End Sub In article <bbkd12tjjfv9qishbo3p629lu8iu8f5g16@4ax.com>, JasonK <JasonK@aol.com> wrote: > seems simple enough, but i can't figure it out. > > i am at a particular random location, and I need to select the cell 5 > rows over....

Sum variable range of column entries in offset cell #2
Hi chaps, Many thanks those of you who tried to help with this question last week, but my simplified example wasn't good enough. I'll ask the question again but with a more detailed explanantion. Sorry for any induced headaches in advance. I have 900 lines of data (A3:AT902). Below this I am using an INDIRECT formula to pull down (copy) certain lines from this mass of data (depending on certain entries within the data) into 60 separate tables below. First table (A915:AT943), second table (A951:AT979), etc. Much of the lines of data copied down into my 60 tables is numeric, and o...

Blank Cell vs Cell equal to Zero (0)
When creating a formula, is there a way to differentiate between a blank cell and a cell that's equal to 0? Here's my formula: =IF('2005 Sales'!O12>0,'2005 Sales'!O12,"") I wanted to change this to if O12 is greater than or equal to 0, then return O12, but I want it to return ("") if the contents of O12 is blank. Is there a way to do this? ... TIA! LavaDude What happens if O12 is *less* then zero? This will leave O12 blank ( "" ) if less then zero: =IF(AND(ISNUMBER(O12),O12>=0),O12,"") -- HTH, RD =========...

Cell formatting in Excel Pivot Diagram
We use Excel 2000 to display a Pivot diagram. The datasource is an olap cube on a SQL server 2000 I would like to format the column headings of the pivot diagram so they are displayed vertically. In excel I go to FORMAT menu, then FORMAT CELLs, then ALIGNMENT. Here I can set the orientation of the header cells to any angle I want (90 degrees). The pivot diagram allows the user to click on a header cell (year) to display the next more detailed level (week number). We have 3 such levels. I expand all levels of the header cells and set the orientation to 90 degrees. After a collapse of the he...