Help with macro/and or formula?

[brefed15]

Perhaps this is a silly question, but i just can't figure out how t
format this worksheet. The problem is that a lot of the cells hav
numbers in them with negative signs in the back instead of in the fron
(ie. 23-) and i was wondering if anyone knew of a macro/formula i coul
use to quickly fix this problem for all the cells. I would b
extremely grateful. Thanks

 

[VBA Noob]

If you don't want to use VBA you could put this formula in the next
cell

Assuming you ar looking at A1 and Forumla in B1

=IF(RIGHT(A1)="-","-"&SUBSTITUTE(A1,"-","",1),A1)

 

[brefed15]

Thank you for the prompt response, but for some reason the formula is
not working. For instance, when i tried it in the wkst for a cell with
2345.12-, it gave me the same exact result. I'm confused cause i think
the formula is right.

 

[Gord Dibben]

Select the cells.

Data>Text to Columns>Next>Next>Advanced.

Checkmark "trailing minus signs etc."

Finish.


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Try this

Sub Test()
Dim oCell As Range

For Each oCell In Selection
If Not IsEmpty(oCell.Value) Then _
oCell.Value = CDbl(oCell.Value)
Next oCell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[brefed15]

Thanks a lot!