Importing data into excel where negative sign apprears after the value

[Tommy]

Hi, I have a specific user problem that I deal with almost
daily & have not been able to come up with a good solution
based on my skill level in Excel.

I must import data into Excel from an accounting program
called Paradigm (Excaliber). In this program values are
formatted such that the negative values have the negative
sign after the number value. Consequently excel does not
recognize this data as valid values when imported into a
worksheet.

I have not been able to come up with any sort of single or
mutltiple step find & replace schemes that will remove the
negative sign after the values and place it in front.

I currently find & replace these signs by looking at the
entire file, usually 2000 lines.

Any ideas?

[J.E. McGimpsey]

Since VBA recognizes the post-fixed negative sign as a valid format,
this macro will convert them to negative numbers that XL will
recognize:

Public Sub ConvertPostNegatives()
Dim myRange As Range
Dim cell As Range

On Error Resume Next
Set myRange = ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
For Each cell In myRange
cell.Value = CDbl(cell.Value)
Next cell
On Error GoTo 0
End Sub

In article <745a01c16877$434c5f00$9ae62ecf@tkmsftngxa02>, Tommy

[Jim Rech]

I would add that Excel 2002 has the ability to do this automatically. I
think it's on by default (it's an advanced option of the Text Import
Wizard). So if you've been looking for a reason to update<g>.....

--
Jim Rech
Excel MVP

[Tommy]

Thanks! It works great & will save me a ton of head aches!

>.
>

[Ragdyer]

Just another alternative - without code:

=LEFT(A1,LEN(A1)-1)*-1

Regards,

RD

"Tommy" <tjy...@hotmail.com> wrote in message
news:735001c16898$2cda3460$3def2ecf@TKMSFTNGXA14...