Formatting Excel Worksheet
 

Please help.
I have a spreadsheet which has various negative numbers entered as follows:
e.g. 5.25-
0.32-
1,502.33- etc etc
Is there any way to format it or a formula which will change the above
numbers so that it is a negtive number
e.g. -5.25,
-0.32, -
1,502.33 etc etc
I have tried all the usual ways to format it but the numbers still remain as
5.25- etc etc

this should do it
Sub fixneg()
For Each c In Selection
If Right(c, 1) = "-" Then c.Value = "-" & Left(c, Len(c) - 1)
Next c
End Sub

--
Don Guillett
SalesAid Software

"KI" wrote in message
...
Please help.
I have a spreadsheet which has various negative numbers entered as
follows:
e.g. 5.25-
0.32-
1,502.33- etc etc
Is there any way to format it or a formula which will change the above
numbers so that it is a negtive number
e.g. -5.25,
-0.32, -
1,502.33 etc etc
I have tried all the usual ways to format it but the numbers still remain
as
5.25- etc etc

if your number is in A1, then use this formula in B1

=IF(RIGHT(TRIM(A1),1)="-",-LEFT(A1,LEN(TRIM(A1))-1),A1)


"KI" wrote:

Please help.
I have a spreadsheet which has various negative numbers entered as follows:
e.g. 5.25-
0.32-
1,502.33- etc etc
Is there any way to format it or a formula which will change the above
numbers so that it is a negtive number
e.g. -5.25,
-0.32, -
1,502.33 etc etc
I have tried all the usual ways to format it but the numbers still remain as
5.25- etc etc

Thanks for your help.
Worked a treat.

"Duke Carey" wrote:

if your number is in A1, then use this formula in B1

=IF(RIGHT(TRIM(A1),1)="-",-LEFT(A1,LEN(TRIM(A1))-1),A1)


"KI" wrote:

Please help.
I have a spreadsheet which has various negative numbers entered as follows:
e.g. 5.25-
0.32-
1,502.33- etc etc
Is there any way to format it or a formula which will change the above
numbers so that it is a negtive number
e.g. -5.25,
-0.32, -
1,502.33 etc etc
I have tried all the usual ways to format it but the numbers still remain as
5.25- etc etc

Thank you.

"Don Guillett" wrote:

this should do it
Sub fixneg()
For Each c In Selection
If Right(c, 1) = "-" Then c.Value = "-" & Left(c, Len(c) - 1)
Next c
End Sub

--
Don Guillett
SalesAid Software

"KI" wrote in message
...
Please help.
I have a spreadsheet which has various negative numbers entered as
follows:
e.g. 5.25-
0.32-
1,502.33- etc etc
Is there any way to format it or a formula which will change the above
numbers so that it is a negtive number
e.g. -5.25,
-0.32, -
1,502.33 etc etc
I have tried all the usual ways to format it but the numbers still remain
as
5.25- etc etc

glad to help

--
Don Guillett
SalesAid Software

"KI" wrote in message
...
Thank you.

"Don Guillett" wrote:

this should do it
Sub fixneg()
For Each c In Selection
If Right(c, 1) = "-" Then c.Value = "-" & Left(c, Len(c) - 1)
Next c
End Sub

--
Don Guillett
SalesAid Software

"KI" wrote in message
...
Please help.
I have a spreadsheet which has various negative numbers entered as
follows:
e.g. 5.25-
0.32-
1,502.33- etc etc
Is there any way to format it or a formula which will change the above
numbers so that it is a negtive number
e.g. -5.25,
-0.32, -
1,502.33 etc etc
I have tried all the usual ways to format it but the numbers still
remain
as
5.25- etc etc