Converting text to numbers

[bankerjohn]

I'm running Excel 2000 ...

After opening a text file with the Text Import Wizard, I hav
successfully segregated a column of numbers into one column. Only on
problem, the NEGATIVE SIGN (for negative numbers in the text file) i
positioned in front of the number, rather than at the end. When th
file import is complete, the negative numbers show up as text, and ar
not included in the column totals.

I believe that this is controlled by the Region Settings in the Contro
Panel ... and if I changed the "Negative Number Format" option fro
"1-" to "-1" ... then I would get the desired results.

However, if there is an easier way to do this with without making
"global" change to the Control Panel, I prefer to leave it "as is." Th
only real problem with changing the Control Panel setting is that ..
undoubtedly the next time I need to import a text file with negativ
numbers ... the negative sign will be at the end of the numbers

 

[RagDyer]

If I am reading correctly, you have a contradiction in your question.

<<the NEGATIVE SIGN (for negative numbers in the text file) is
positioned in front of the number, rather than at the end>>

AND

<<if I changed the "Negative Number Format" option from
"1-" to "-1" ... then I would get the desired results.>>

What exactly are you looking to accomplish ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


I'm running Excel 2000 ...

After opening a text file with the Text Import Wizard, I have
successfully segregated a column of numbers into one column. Only one
problem, the NEGATIVE SIGN (for negative numbers in the text file) is
positioned in front of the number, rather than at the end. When the
file import is complete, the negative numbers show up as text, and are
not included in the column totals.

I believe that this is controlled by the Region Settings in the Control
Panel ... and if I changed the "Negative Number Format" option from
"1-" to "-1" ... then I would get the desired results.

However, if there is an easier way to do this with without making a
"global" change to the Control Panel, I prefer to leave it "as is." The
only real problem with changing the Control Panel setting is that ...
undoubtedly the next time I need to import a text file with negative
numbers ... the negative sign will be at the end of the numbers!

 

[Peo Sjoblom]

Excel 2002 onwards will fix trailing minuses automatically when you use the
text import wizard,
otherwise you'll have to use a help column or a macro

Formula

=IF(RIGHT(A1,1)="-",---LEFT(A1,LEN(A1)-1),A1)


Macro

Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, (e-mail address removed)
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub


to use the formula just use a help column and copy down adjacent rows and
then paste special as values over the old
values. Then delete help column

For macro open workbook, press Alt + F11, click insert>module and paste it
in, close VBE with Alt + Q and save WB,
then select the imported values, press Alt + F8 and run macro

 

[bankerjohn]

The formula (and a "help" column) worked great ... thanks. I need to g
ahead an install Office XP to rid myself of this issue!

Excel 2002 onwards will fix trailing minuses automatically whe
you use the
text import wizard,
otherwise you'll have to use a help column or a macro

Formula

=IF(RIGHT(A1,1)="-",---LEFT(A1,LEN(A1)-1),A1)