Converting a number with a trailing minus sign

[howard]

I have the folowing number 1235.75- and want to convert this into a number value to appear as (1,235.75)

I have used the folllowing formula

=TEXT(VALUE(RIGHT(A23,1)&LEFT(A23,FIND("-",A23)-(1))),"(#,##0.00)")

The results in -(1,235.75)

It would be appreciated if someone can amend my formula so as to give me (1,235.75)

 

[VoG]

Try

=TEXT(ABS(VALUE(RIGHT(A23,1)&LEFT(A23,FIND("-",A23)-1))),"(#,##0.00)")

 

[howard]

Hi Peter

Thanks for the help

 

[xenou]

Not tested but this also comes to mind (converting it to a real number - then apply the usual number formatting):

=IF(ISNUMBER(A1),VALUE(A1),-VALUE(Left(A1,Len(A1)-1)))

 

[Jonmo1]

You can also use Data - Text to columns.
On 3rd screen, click Advanced, and make sure there is a check on
Trailing minus for negative numbers

 

[T. Valko]

I have the folowing number 1235.75- and want to convert this into a number value to appear as (1,235.75)

I have used the folllowing formula

=TEXT(VALUE(RIGHT(A23,1)&LEFT(A23,FIND("-",A23)-(1))),"(#,##0.00)")

The results in -(1,235.75)

It would be appreciated if someone can amend my formula so as to give me (1,235.75)

You can do this without using a formula (maybe!).

Select the range of cells in question
Goto Data>Text to Columns
Click Finish

Format the cells as desired

 

[howard]

Hi Guys

Thanks for all the input, much appreciated