I'm transferring a file from Mainframe into Excel where I can manipulate the
data. Everything was working just great until I realized to my shock and
dismay, that negative numbers in Mainframe are (ex.) 16- instead of -16,
which means they are formatted as text when they import into the spreadsheet.
I've tried everything I can think of to format these numbers as values
without success. Any ideas besides re-keying the numbers which I really
don't want to do? TIA

You can insert a column beside the numbers and use a formula like this:
=IF(RIGHT(A1,1)="-",-LEFT(A1,LEN(A1)-1),A1)
This will change the sign of the numbers ending in "-" and leave the other
the same.
Then you can copy/paste values on the original column and delete the added
one.
Hope this helps,

Miguel.

"cottage6" wrote:

I'm transferring a file from Mainframe into Excel where I can manipulate the
data. Everything was working just great until I realized to my shock and
dismay, that negative numbers in Mainframe are (ex.) 16- instead of -16,
which means they are formatted as text when they import into the spreadsheet.
I've tried everything I can think of to format these numbers as values
without success. Any ideas besides re-keying the numbers which I really
don't want to do? TIA

Transform the text (supposed to be in column A) with this formula into a new
column:
=IF(RIGHT(A1,1)="-",-VALUE(SUBSTITUTE(A1,"-","")),VALUE(SUBSTITUTE(A1,"-","")))

Fill down as required!

Regards,
Stefi

€žcottage6€� ezt Ã*rta:

I'm transferring a file from Mainframe into Excel where I can manipulate the
data. Everything was working just great until I realized to my shock and
dismay, that negative numbers in Mainframe are (ex.) 16- instead of -16,
which means they are formatted as text when they import into the spreadsheet.
I've tried everything I can think of to format these numbers as values
without success. Any ideas besides re-keying the numbers which I really
don't want to do? TIA

If you temporarily change the regional settings for numbers on your PC to
recognize negative signs at the end of numbers you will be okay I believe.
Then copy and paste values and change the regional settings back.

"cottage6" wrote:

I'm transferring a file from Mainframe into Excel where I can manipulate the
data. Everything was working just great until I realized to my shock and
dismay, that negative numbers in Mainframe are (ex.) 16- instead of -16,
which means they are formatted as text when they import into the spreadsheet.
I've tried everything I can think of to format these numbers as values
without success. Any ideas besides re-keying the numbers which I really
don't want to do? TIA

cottage6
or...
=IF(ISNUMBER(B7),B7,---LEFT(B7,LEN(B7)-1))
beege

"cottage6" wrote in message
...
I'm transferring a file from Mainframe into Excel where I can manipulate
the
data. Everything was working just great until I realized to my shock and
dismay, that negative numbers in Mainframe are (ex.) 16- instead of -16,
which means they are formatted as text when they import into the
spreadsheet.
I've tried everything I can think of to format these numbers as values
without success. Any ideas besides re-keying the numbers which I really
don't want to do? TIA

If using Excel 2002 or 2003 select number cells and DataText to
ColumnsNextNextAdvanced"trailing minus for negative numbers" must be
checked.

Hit Finish


Gord Dibben MS Excel MVP

On Tue, 25 Apr 2006 06:54:01 -0700, cottage6
wrote:

I'm transferring a file from Mainframe into Excel where I can manipulate the
data. Everything was working just great until I realized to my shock and
dismay, that negative numbers in Mainframe are (ex.) 16- instead of -16,
which means they are formatted as text when they import into the spreadsheet.
I've tried everything I can think of to format these numbers as values
without success. Any ideas besides re-keying the numbers which I really
don't want to do? TIA