format cells not to except dashes
I am trying to format a range of cells not to except dashes (-) ie. when
entering phone numbers 123-123-1234
Format is a display function only, it doesn't affect how entries are
parsed (I assume you mean "accept").
You can use validation. Select your range (I'll assume that A1 is the
active cell). Choose Data/Validation/Allow:Custom with the formula
In article <6ADB6887-0F6C-4D23-A2D4-CCCFC69CD006@microsoft.com>,
"kclover" <email@example.com> wrote:
> I am trying to format a range of cells...how to format cells so that it only shows thousands.
i want to format cells so that
1,234,567 = 1,234 or 1235
how can i do this other than divide by 10.
i just want to use the format number feature.
Message posted from http://www.ExcelForum.com
try the custom format
>i want to format cells so that
>1,234,567 = 1,234 or 1235
>how can i do this other than divide by 10.
>i just want to use the format number feature.
>Mess...Format Cells, Text Alignment, Distributed
I have a cell that I need to format so that it is justified but I need to
control where the text breaks within the cell. For example, I have in cell
A1 "Name: Jeff Willis Date: 6/16/2007". The cell width is fairly wide and I
would like to format the cells Horizontal Text Alignment to Distributed.
Is there a way to not make the cell distribute the text evenly? For
example, can I embed a tab charactor or some other workaround between the
's' in Willis and the 'D' in Date to make span something like this
Name: Jeff Willis
Date: 6/16/20...Format cells with a formula (7 conditions).
I want to format groups of cells with a different color according to the day
of the week specified in one of the cells. Conditional formatting allows only
4 different conditions. Is there a way to use a formula to set the cell color?
Put these Codes in sheet module and you can change colors as yo
Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlC...format cells #6
I have solved this so it is just my curiosity at play here.
Using someone else's sheet, I entered a formula (=D235/80) into a cell
expecting a numeric result not realizing that the cell was formatted as Text
(so I got to see the formula as text). So I changed the cell format to
Number with 4 places.
To my surprise, the cell still displayed the formula. I looked for a leading
' or space but there was none. Retyping the formula produced the result I
expected in the first place.
What is going on?
Changing the forma...Format Cells
I have a worksheet in which I want a column formatted so that I can put
in a value that looks for like 10-11 or 2-3. If I use the Format Menu
and choose Cells and specify General, as soon as I press enter, the
value in the original cell changes to 11-Oct or 3-Feb and the format has
changed to Custom d-mmm. I have tried selecting the column and choosing
Edit-Clear-Formats but that doesn't help. I would appreciate any
suggestions as to how to solve this problem. I don't think it existed
when I first created the worksheet but has now appeared magically!
either for...format cells option not working
when i highlight some cells and try to format them nothing happends,
workbook and sheets are not protected, cells in question dot not have any
data in them at all, totaly blank!
if they are locked how can i findout? and how to unlock them?
OFF: Changes to Fill Color and Fill Pattern Are Not Displayed
Regards Ron de Bruin
"Steven" <firstname.lastname@example.org> wrote in message news:K3Yfc.2505$UG1.email@example.com...
> when i highlight some cells and...reformat cells
1. I have numbers in cells A1 through A6. I want to copy those numbers
into cells B1 through G1. How do I reformat the cells so that they list
Note: I don't want to format the whole column.
2. How do I open two instances of excel on two different screens?
How about just selecting a1:A6
edit|paste special|check Transpose.
I'd put another shortcut on the desktop that starts excel and click that
(and hit enter)
> 1. I have numbers in cells A1 through A6. I want to copy ...Compare fields and highlight the entries with Format Cells.
Seems like an easy task but don't know how. Can somebody help?
I have to compare the entries of column A,B,C against that of column E,F,G.
I did the EXACT function as follows:
=EXACT(A1,E1) and so on.
and filtered for FALSE. But I also want to automatically Fill Color for the
cells where the entries don't match?? Is there ONE formula to do so?
Select A1:C9999 (with A1 your activecell)
then do the same thing with E1:G9999
> Seems like an easy task but don't know ...Format Cells based on 1 or 2 Cell(s)
I have a spreadsheet with a combo box in the first two
columns (selections are Create, Delete, and Modify). I
would like to set the conditional formatting to change
Cells A:AE green for Create, Red for Delete and Yellow for
Modify for the particular row.
I have tried the Conditional Formatting option, using the
Formula Is option, with the formula ="Create" and =If
($A:$AE="Create) and neither works. I have been building
a database all day and can't see straight, which could be
part of the problem.
Any one out there able to help a guy stuck in T.O.?
Thanks Muc...Where are "Custom Format Cells" stored ?
Do you know where are stored the data of "format cells" dialog box ?
"custom" formats are not present in excel 2002, for some of my users...
Thank you !
First thing you do when you come back from lunch.
Make a change to custom formats then run a file search for any file changed
in the last few minutes/hour
> Do you know where are stored the data of "format cells" dialog box ?
> "custom" formats are not present in excel 2002, for some of my users...
> Thank you !
&g...How do I format cells so that room numbers such as 1402, 1405a will sort correctly?
I'm trying to fix an Excel spreadsheet for a health care facility so that it
can sort data by the first column, whci is the patient's room number. Room
numbers can be pure numerical, or have a suffix at the end (a or b). So I
have e.g. room numbers 1306, 2204, 1401b, 1613, 1402b. At the moment, the
column sorts so that all the room numbers with a suffix follow all the
numbers without a suffix. I've formatted the cells as General, Numbers, or
Text, and get the same sort result each time. How can I fix this? HELP!
All help gratefully received and acknowledged.
Dan E...too many different format cells
There is something i have been met first time in my carrier, which is not
big, and it is very strange.
That it looks like....
I have had two workbooks and i create a third one by copying some sheets
from each book. Yesterday i tried to copy another one sheet to my workbook,
but the copied sheet lost its format and the program crashed. So when i
reopen the excel the workbook worked quite good except the sheet which has
lost its. format. When i tried to change the size of a word it shows me that
message " Too many different format cells". and i couldn't made any
differ...How to format cells for uppercase entry
I need to format a column so that all cell force any text entry to
uppercase. I've looked into the UPPER() function, but that is something that
would actually take the value from some otther location and display it in
uppercase where the fuction is located, which will not work.
What I'm looking for is something similar to Format->Cells->Numbers, which
allows you to define a format for numbers, so that however a number is
entered in the cell it automatically displays it in the desired format. So
I'd like to be able to have the data entry be in upper or lower case, but
...How do I format cells in Excel to make 175/5 read 175?
I want to copy a list of numbers from a web site into a spreadsheet. The
numbers all have a / in them, and this copies to the spreadsheet. Example
195/5. I would like to be able to format the cells so that the numbers will
read 195 (losing the / and any digits after it. Using another column with a
formula that would do this will also work.
Select the column and go to Data > Text to columns > Delimited. Select / as
Microsoft Excel MVP
"SJR4347" <SJR4347@discussions.mic...how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show
just the date and not the time. Going into format and clicking on the date
and changing doesnt work.
Probably the cells aren't really dates, but text. You can check with the
If they are text, formatting doesn't have any effect.
Microsoft MVP - Excel
"bondam" <firstname.lastname@example.org> wrote in message
>I want to format a column that contains date and time and I want it t...Is it possible to format cells in cms
Does anyone know if it is possible to format cells in cms rather tha
points. I am trying to size some cells to a specifice height fo
printing. Can this be done or am I at the mercy of MS and have to jus
guess the size.
Any help would be much appreciated.
erdie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1551
View this thread: http://www.excelforum.com/showthread.php?threadid=27085
On Wed, 20 Oct 2004 10:44:31 -0500, erdie <email@example.com>
...Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit.
Assuming the first date is in A1
C1: =B 1+1
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Pat" <firstname.lastname@example.org> wrote in message
> Is there a way to format cells so that dates would change when the lead
date is...format cells #4
hi. how can i set by default the cell formatting to 'general' or
'number' or even 'text'?
i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to
09.dec. is this possible?
(i have some programs which export data to excel and instead of numbers
in excel appear dates and i can't set the cell format before the data is
notrace2004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34817
View this thread: http://www....Format cells, category, postion
Is there anyway to make a number into a postion (e.g. add
the st in 1st, or nd in 2nd), the difficult is that it
needs to go on a cell with a formula in, so it can change
from 1, 2, 3 etc.
You can't do this as a formatting, but you could add a formula to
another cell: with a number greater than zero in cell A1 to return the
","rd","th","th","th","th","th","th")),""...Format Cells and a whole lot!
i have a sheet with only 1 column (A) each cell downward has an entry
and it looks like this
1 email@example.com,"Ernest Vasquez"
and so forth and so forth
i need to remove the ,"Ernest Vasquez" from this cell and paste on a
separate column (B) without the , and " " just the plain name. Why
not just highlight cell A1 and manually delete the characters?
Well because there are 25,213 entries from A1 to A25213. Gosh!!!
djranks's Profile: http://www.ex...Format Cells?
Not sure if this is the easiest way to do this but I am wanting to create a series of cells
Is there an easy method of doing this perhaps with a Format function or will I have to create a sum to work out the
last cell plus .0.1?
Any help is appreciated.
Give this a try in any starting cell, eg in A1:
"@Homeonthe...How format cells ?
I want format money
for example '12,35 z�' - i want to show this as '12.35 z�'
How i may write fiormula to this work ?
ps. Soory but my english is poor
#,##0.00"z then hit ALT + 0179(on numpad) then add another "
Gord Dibben Excel MVP
On Tue, 22 Nov 2005 15:30:51 +0100, "Fantom"
>I want format money
>for example '12,35 z�' - i want to show this as '12.35 z�'
>How i may write fiormula to this work ?
>...Like to format cells
I like to format the cell so that #DIV/0 wouldn't apper in the cell i
parent data is not available yet.
A1 = A2/A3
BI = B2/B3
C2 = C2/C3
and so on.......
data for A2,A3,B2,B3 are available but
data for C2,C3,D2,D3...... are NOT available yet.
I want to see blank cell if C2,C3,D2,D3.....are not filled yet.
Any help would be appreciated
Message posted from http://www.ExcelForum.com
in c1 enter =IF(ISERROR(C2/C3),0,C2/C3)
"NYBoy >" <<NYBoy.firstname.lastname@example.org> wrote in message
> I like to form..."Format cells" not working
I have some Excel files in which "Format Cells" is not working. I canno
bring up the format cell box either through Ctrl-1, the task bar, o
right-clicking the mouse. While "Format Cells" works in other Exce
files, as soon as anything is copied from one of the "infected" file
to another file where "Format Cells" was working, "Format Cells" stop
working in these other files as well.
I am using Excel 2000 and Windows XP. Any ideas before I hurl my lapto
from my 79th floor office
Message posted from http://www.ExcelForum.com
Your sheet ...