Help with macro/and or formula?

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

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)

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.

Select the cells.

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

Checkmark "trailing minus signs etc."

Finish.


Gord Dibben MS Excel MVP

On Mon, 19 Jun 2006 13:02:02 -0500, brefed15
<brefed15.29nvin_1150740605.7171@excelforum-nospam.com> wrote:

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

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" <brefed15.29nwob_1150742106.8707@excelforum-nospam.com> wrote in
message news:brefed15.29nwob_1150742106.8707@excelforum-nospam.com...
>
> 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.
>
>
> --
> brefed15
> ------------------------------------------------------------------------
> brefed15's Profile:
http://www.excelforum.com/member.php...o&userid=35573
> View this thread: http://www.excelforum.com/showthread...hreadid=553339
>

Thanks a lot!