Data import of negative numbers shown as text

[Ginny]

When I download data from our mainframe and import into
Excel 2000, the negative numbers are shown with the minus
sign after the number and therefore come over as text.
Does anyone know of a way to find these cases in a long
list of data and convert them to numbers with the minus
sign preceding the number?

Example:
152.26-

I would appreciate if someone could help me with this.
Thanks

 

[Frank Kabel]

Hi
try
- select your column
- goto 'Data - Text to columns'
- go through the wizard
- finish the wizard

 

[Peo Sjoblom]

That will not work in 2000, it was introduced in 2002.

so either a formula in a help column

=IF(ISNUMBER(FIND("-",A1)),---SUBSTITUTE(A1,"-",""),A1)

or VBA

Sub Conv_Trail_Minus()
Dim cell As Range
For Each cell In Selection.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
cell.Value = CDbl(cell.Value)
Next cell
End Sub


Also note that to use it in 2002/2003 you don't need to go through the
wizard unless you have changed the default settings, just open the wizard
and click finish

 

[Jim Rech]

Excel 2002 has a built in way to deal with number like yours but with Excel
2000 I might try creating a new column of formulas like this:

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

Substitute your first row and column for the A1 in this formula. Then you
can copy this formula down the length of your list. Lastly, Copy the
formulas and do an Edit, Paste Special, Values to replace these formulas
with their values.

--
Jim Rech
Excel MVP
| When I download data from our mainframe and import into
| Excel 2000, the negative numbers are shown with the minus
| sign after the number and therefore come over as text.
| Does anyone know of a way to find these cases in a long
| list of data and convert them to numbers with the minus
| sign preceding the number?
|
| Example:
| 152.26-
|
| I would appreciate if someone could help me with this.
| Thanks

 

[Ginny]

You are wonderful!!!
I tried the formula and it worked beautifully.
I tried for hours to write a formula to do this but with
no success. Thanks for your help!

 

[Frank Kabel]

Hi Peo
thanks for this (thought it was added in Excel 2000). Though in my
Excel version (without changing any settings) I have to step through
the wizard to make this work 8could be a regional settings thing)

 

[JE McGimpsey]

Could probably get away with one - :

=IF(ISNUMBER(FIND("-",A1)), -SUBSTITUTE(A1,"-",""),A1)

(but far be it from me to recommend not using a double unary minus! <g>)

 

[Peo Sjoblom]

LOL! I guess I have it on my brain

 

[Peo Sjoblom]

Frank,

that is interesting, so if you just step through it and click finish it
works but not
if you click finish immediately or do you have to click advanced and check
the trailing minus for negative numbers?

 

[Frank Kabel]

Hi Peo
I don't have to go to 'Advanced' but I just have to click OK two times.
Clicking immediatly 'Finish' won't do. It's probably my German version