G
Guest
I am trying to set a column to have only numbers and reject or spit out anything else.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
Frank Kabel
Hi Tim
select the column and goto 'Data Validation' and choose 'Number'
HTH
Frank
select the column and goto 'Data Validation' and choose 'Number'
HTH
Frank
B
Bob Phillips
See my response to your previous post, and please only ask the question
once.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
once.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
anything else.Tim said:I am trying to set a column to have only numbers and reject or spit out
D
David McRitchie
Hi Tim,
you want Validation
Validate / Validation
http://www.mvps.org/dmcritchie/excel/validation.htm
When doing Data Validation you select a range, the formulas will be
applied to the entire range based on the formula in the active cell,
which would normally be the first cell in the range.
Select Column B
Data, Validation, custom, formula is: .
your test could be
=ISNUMBER(B1)
--
you want Validation
Validate / Validation
http://www.mvps.org/dmcritchie/excel/validation.htm
When doing Data Validation you select a range, the formulas will be
applied to the entire range based on the formula in the active cell,
which would normally be the first cell in the range.
Select Column B
Data, Validation, custom, formula is: .
your test could be
=ISNUMBER(B1)
--
G
Guest
Thanks everyone but i tried each soln and the words stay in the column?
D
David McRitchie
Until you enter a correct value -- a number, if you hit "Retry"
you type over the selection; if you use "Cancel" button
you can start over as if you had done nothing.
When you create the validation there is additional
information that you can provide by choosing a tab
on that dialog.
What do you want to happen, perhaps you would prefer
and Event macro, where something is actually entered
if you mess up.
http://www.mvps.org/dmcritchie/excel/event.htm
you type over the selection; if you use "Cancel" button
you can start over as if you had done nothing.
When you create the validation there is additional
information that you can provide by choosing a tab
on that dialog.
What do you want to happen, perhaps you would prefer
and Event macro, where something is actually entered
if you mess up.
http://www.mvps.org/dmcritchie/excel/event.htm
F
Frank Kabel
Tim said:Thanks everyone but i tried each soln and the words stay in the
column?
Hi Tim
you have to apply the data -validation prior to entering data into the
cells. Applied after entering does not work. Excel will only re-check
after re-entering data in this cell
Frank
G
Gord Dibben
Tim
Data Validation prevents entry of anything but your criteria as pointed out i
other posts.
It will not delete/clear previously entered items.
That will have to be done manually or through VBA.
Gord Dibben Excel MVP
Data Validation prevents entry of anything but your criteria as pointed out i
other posts.
It will not delete/clear previously entered items.
That will have to be done manually or through VBA.
Gord Dibben Excel MVP
D
David McRitchie
To identify cells in Column B that are not numbers you can use
Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
Select Column B with cell B1 as the active cell
Format, Conditional Formatting, formula is:
=AND(ROW()<>1,ISTEXT(B1)
choose format, use patterns tab to supply a background color
such as a pastel color.
Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
Select Column B with cell B1 as the active cell
Format, Conditional Formatting, formula is:
=AND(ROW()<>1,ISTEXT(B1)
choose format, use patterns tab to supply a background color
such as a pastel color.