How to correct negative number format

[Mojosixx]

Many times our system gives us #’s like the first column but it can’t be changed to currency because of the negative sign being after the #. How can I change the first column to look and function like the second?

[TABLE="width: 145"]
<tbody>[TR]
[TD]25-
[/TD]
[TD]$ (25.00)
[/TD]
[/TR]
[TR]
[TD]45-
[/TD]
[TD]$ (45.00)
[/TD]
[/TR]
[TR]
[TD]81-
[/TD]
[TD]$ (81.00)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Is it only the negative numbers that show like that? If so, try Text2Columns/Delimited/Other - (minus sign)

If you have + and +, then maybe thus ugly thing...

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]25-[/td][td]

-25​

[/td][/tr]

[tr][td]

2​

[/td][td]45-[/td][td]

-45​

[/td][/tr]

[tr][td]

3​

[/td][td]85-[/td][td]

-85​

[/td][/tr]

[tr][td]

4​

[/td][td]25+[/td][td]

25​

[/td][/tr]
[/table]
B1=--(RIGHT(A1,1)&LEFT(A1,LEN(A1)-1))
copied down

(Im sure someone will have a better way than that)

 

[Rick Rothstein]

Is it only the negative numbers that show like that? If so, try Text2Columns/Delimited/Other - (minus sign)

If you have + and +, then maybe thus ugly thing...

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]25-[/td][td]

-25​

[/td][/tr]

[tr][td]

2​

[/td][td]45-[/td][td]

-45​

[/td][/tr]

[tr][td]

3​

[/td][td]85-[/td][td]

-85​

[/td][/tr]

[tr][td]

4​

[/td][td]25+[/td][td]

25​

[/td][/tr]
[/table]
B1=--(RIGHT(A1,1)&LEFT(A1,LEN(A1)-1))
copied down

(Im sure someone will have a better way than that)

I'm not sure its better, but as long as each entry has a trailing plus or minus symbol, you could also do it this way...

=--MID(A1&A1,LEN(A1),LEN(A1))

 

[Scott Huish]

With Text to Columns, on the Delimeter screen deselect everything.
Click Next.
Click on the Advanced button.
Make sure trailing minus for negative numbers is checked.
Click Finish