Text file shows negative number with mark on the right

[Stahr627]

I import a text file from DB2 to Excel in order to manipulate the data for a report. It gets imported as a text file, and there are a couple of columns that show negative numbers, with the hyphen on the right. When these columns are imported, they register as text, not a number, and I need a formula or a macro to change all the instances to show a negative number instead. The files are fairly large, and it would take forever to manually change the problem.
Thanks!

Gail Dengler
GDengler@boscovs.com

 

[Imagineer]

Possible procedure.
Assuming you can export to a fixed length line. Excel has a text to column feature. You basically set which characters in a line go in which column. For example, columns 1-7=A; 8-13=B; 14=C; 15-26=D. Choose Data|Text to Columns. A dialogue box appears. Choose Fixed Width. Click Next. Then click where you want the columns. Isolate the negative sign in one column. Then in an empty column, use the following function "=if(C3="-",-B3,B3)". Select the newly created column. Paste Special as Values onto the appropriate column (column B in my example).

Or: If you can't export fixed length lines.
Import the file. Let the column import as text. Then in an empty column use: =if(right(B3)="-",-value(left(len(b3)-1)),value(b3)). Copy and Paste Special as above.

Additionally, both methods will still work if the negative sign is on the left.

Imagineer

 

[Stahr627]

Thanks for the quick response!! I came to the same conclusion to solve the problem, but being a relative "newbie" to Excel, I think I was afraid to believe I actually had solved the problem so easily. I appreciate your help, it boosted my self-confidence!


Gail Dengler
GDengler@boscovs.com