Help with macro/and or formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brefed15 - 19 Jun 2006 19:02 GMT

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

Reply to this Message

VBA Noob - 19 Jun 2006 19:19 GMT

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)

Signature

VBA Noob

Reply to this Message

brefed15 - 19 Jun 2006 19:32 GMT

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.

Signature

brefed15

Reply to this Message

Bob Phillips - 19 Jun 2006 20:09 GMT

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

Signature

HTH

Bob Phillips

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

> 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.

Reply to this Message

Gord Dibben - 19 Jun 2006 20:00 GMT

Select the cells.

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

Checkmark "trailing minus signs etc."

Finish.

Gord Dibben MS Excel MVP

>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!

Reply to this Message

brefed15 - 19 Jun 2006 20:15 GMT

Thanks a lot!

Signature

brefed15

Reply to this Message