excel column only numbers

[Guest]

I am trying to set a column to have only numbers and reject or spit out anything else.

 

[Frank Kabel]

Hi Tim
select the column and goto 'Data Validation' and choose 'Number'

HTH
Frank

 

[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)

I am trying to set a column to have only numbers and reject or spit out

anything else.

 

[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)
--

 

[Guest]

Thanks everyone but i tried each soln and the words stay in the column?

 

[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

 

[Frank Kabel]

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

 

[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

 

[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.