Help needed - text import wizard / formatting numbers

[doodoo]

hi all, new to the forums as i need help!

running excel 2000 under advanced server 2000 (intel p4 2ghz hp compa
evo pc) and having a problem with the text import wizard ;

trying to import a fixed column text report with three number column
that include negative values in the format 320.06-

problem is that they have to be imported as general type as there is n
number option in the import wizard which then means that excel appear
to treat the negative sign as text pushing the cell justification ove
to the left and if i try to create a cell which displays the sum o
these three negative values, the result is always zero.

tried formatting the columns in all the different number, custom
general formats etc but nothing works.

do i need to create a new custom format?

any suggestions please?

thanks in advance :

 

[Ron Rosenfeld]

hi all, new to the forums as i need help!

running excel 2000 under advanced server 2000 (intel p4 2ghz hp compaq
evo pc) and having a problem with the text import wizard ;

trying to import a fixed column text report with three number columns
that include negative values in the format 320.06-

problem is that they have to be imported as general type as there is no
number option in the import wizard which then means that excel appears
to treat the negative sign as text pushing the cell justification over
to the left and if i try to create a cell which displays the sum of
these three negative values, the result is always zero.

tried formatting the columns in all the different number, custom,
general formats etc but nothing works.

do i need to create a new custom format?

any suggestions please?

thanks in advance

Custom Format will not help.

I don't know when this appeared, but Excel 2002 does have that capability in
the Text to Columns Wizard.

Select your data and, when you get to Step 3, see if there is an Advanced
button. One of the options is 'Trailing Minus for negative numbers'.

If not there, one solution is to use a "helper" column to convert the data.

With data in A1:An, enter formula: =IF(RIGHT(A1,1)="-",-LEFT(A1,LEN(A1)-1),A1)
in helper column and copy/drag down as needed.

Then select the helper column, and Edit/Paste Special/ Values over the data in
Column A




--ron

 

[doodoo]

excellent!

thanks ron, appreciate your help - i'll use the helper column you
suggested for now and will request the office upgrade for the future.

many thanks, much appreciated!

 

[Dave Peterson]

Dana DeLouis posted this:


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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[doodoo]

wow, these forums are excellent! thanks guys for all the help, i'm well
impressed!