Text to value

[cottage6]

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

 

[Miguel Zapico]

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.

 

[Stefi]

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:

 

[William Horton]

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.

 

[Beege]

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

 

[Gord Dibben]

If using Excel 2002 or 2003 select number cells and Data>Text to
Columns>Next>Next>Advanced>"trailing minus for negative numbers" must be
checked.

Hit Finish


Gord Dibben MS Excel MVP