Hi I am trying to figure out a formula that allow me to avoid sign
carachters such "#< $ % &" being enter as data what can I do? Help me!

"Shane Devenshire" wrote:

Hi,

1. First let me simplify one of the formulas

=IF(B1,A1/B1,0)

2. If the cells really contain trailing spaces you can remove all of them
with a single command - highlight all the offending cells and press Ctrl+H,
enter three spaces in the Find what box and leave the Replace with box
empty. Then click Replace all. The cells will still be text most likely,
but they will not contain 3 trailing spaces.

3. You can use the VALUE function to convert text numbers followed by
trailing spaces to numbers =VALUE(A1) However, this might not work if the
trailing spaces were not really spacebar spaces. In which case you could
try =VALUE(TRIM(CLEAN(A1)))

4. However, maybe the following solution will be best:

Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being
entered as text. Numbers can be stored as text by 1. preformatting the cell
to Text and entering the number, 2. Typing an apostrophy in front of the
number '123, 3. Because the data was downloaded from a soure inwhich it was
stored as a text number, 4. Because you used the Text to Columns command and
converted it to text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles
may appear at the top left corner of these cell, but this feature may be off
or the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers.
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

If this information is helpful, please click the Yes button.

Cheers,
Shane Devenshire

"lypolintan74" wrote in message
...


"T. Valko" wrote:

You can install this macro, select the import and run the macro

You forgot the macro!

Did you mean this one?

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
It's not a good way of handling DIV errors because it will hide other
errors

It's better to use

=IF(B1=0,0,A1/B1)


You can install this macro, select the import and run the macro

--


Regards,


Peo Sjoblom

"LHW" wrote in message
...

I understand how the ISERROR() function handles division by zero in
your
example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to
the
numeral 0. However, the problem I have encountered is that numbers
are
downloaded as text with three trailing spaces. I have to manually
remove
these trailing spaces to use these numbers in the calculation. This
is a
time consuming task.

Is there a better way to automatically convert numbers stored as text
to
numeric format? I suppose I could write a macro to automatically
remove
three characters from the end of each item in a one-dimensional array
(in
my
case), but I've never written a macro in Excel 2007. Can the VALUE
function
not be used to convert text to numbers? I have had no luck using it
to
do so.

Any help would be greatly appreciated.

Best wishes,

Lewis Williams

"JLatham" wrote:

"wrap" your formula inside of an IF statement with a test to see if
it
creates an error or not. Consider this

A B C
1 5 0 =A1/B1
will give you a #DIV/0 error

But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value
then
you'd
get the true answer. You can substitute a value for "invalid" such
as
zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)

The ISERROR() test will handle all errors, there are other options
such
as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and
Hlookup
operations.

"SkiRJB" wrote:

Is there a way to get a formula to return a value instead of an
error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get
a
value
at some where other than zero?