Formatting Cells/Data
 

Hi, I hope you are doing well.

I have a file I downloaded from our AS/400 (OS400 operating system). I open
the file as a text file and go thru the wizard for converting the file to
excel format. One of the columns/fields is numeric with some neg and pos
numbers and some blank cell contents. During the wizard conversion the field
is categorized as DOUBLE with no option to change the field. When I finish
the wizard I save the file as an Excel workbook. Then I use the autosum
option to calculate the total of the field/column; however, it only picks up
on the positive #'s. Hence the total is off by the sum of the negative #'s.
I've used the FORMAT cells option to format the column/cells from top to
bottom as number but the cells with the negative #'s remain left justified
and still are not calculated in the total.

Does anyone know what I can do to convert these cells to a number I can use
for calculating the total? Thanks in advance.

Ken

Hi Ken-

I would think the first step is to more closely examine one of the cells
containing what should be a negative and determine what is causing the data
to be interpreted as text - that's what is preventing the numbers from being
subtracted in your total.

Once you isolate that it may be a simple matter of find & replace or it may
be more involved.

HTH |:)

"Ken" wrote:

Hi, I hope you are doing well.

I have a file I downloaded from our AS/400 (OS400 operating system). I open
the file as a text file and go thru the wizard for converting the file to
excel format. One of the columns/fields is numeric with some neg and pos
numbers and some blank cell contents. During the wizard conversion the field
is categorized as DOUBLE with no option to change the field. When I finish
the wizard I save the file as an Excel workbook. Then I use the autosum
option to calculate the total of the field/column; however, it only picks up
on the positive #'s. Hence the total is off by the sum of the negative #'s.
I've used the FORMAT cells option to format the column/cells from top to
bottom as number but the cells with the negative #'s remain left justified
and still are not calculated in the total.

Does anyone know what I can do to convert these cells to a number I can use
for calculating the total? Thanks in advance.

Ken