Discussion Locked

Excel "Text to Column" of format 123.45-

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Bringing data from mainframe with negative numbers showing the minus sign on the right. Is there a way to make Excel recognize this as a numeric number? Format trick, macro, whatever. Thanks

5 replies

by kirill lozovatsky

Column A contais your data:

A1:123.44-
A2:456.74
A3:234.98-

Than in column B inser such formula:

B1: =IF(RIGHT(A1,1)="-",-VALUE(MID(A1,1,LEN(A1)-1)), VALUE(A1))
B2: ....
B3: ....

After that your's column B will hold all values form yours column A. Than you can use Copy/Paste Special/Value to copy an insert a values, or jast use in your formulas column B insted of A.

by tbr

If Mainframe to PC is a daily task you may want to automate the entire process with a Datamining software like Datawatch's MONARCH - in your case the column of info (123.45-) would be formatted for a numeric data type and then exported to Excel or Access without a hitch..Monarch is excellent for reports that subtotal\total by product, cost center, workcenter, etc..

This discussion is closed to new replies.