Thanks.

I seemed to have opened to reply to group windows and I pasted the link into
the one that I closed <g

--


Regards,


Peo Sjoblom

"T. Valko" wrote in message
...
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?