Formula to move trailing minus

[howard]

I have the following value in col K 7,837.52-

I have tried using the formula

=IFERROR(VALUE(RIGHT(K63,1)&LEFT(K63,FIND("-",K63)-1)),VALUE(K63))

To remove the trailing minus and to put it in front of the value, but I get #value!

I have shown sample data below. It would be appreciated if you could assist me

Excel Workbook
	K	L	M
63	7,837.52-	24	#VALUE!
64	1,381.68-	24	#VALUE!
65	1 389.55	25-	1 389.55
Sheet1

 

[tweedle]

Try:
M63: =IF(RIGHT(K63,1)="-",(-1)*VALUE(LEFT(K63,FIND("-",K63)-1)),VALUE(K63))

 

[Jonmo1]

Instead of a formula, try

Highlight the column of numbers
Click Data - Text to columns
Select Deliminated
Finish

 

[jmthompson]

=if(right(a1,1)="-",concatenate("-",substitute(a1,"-","")),a1)

 

[howard]

Hi Guys

Thanks for the reply. I still get #value!. However, If I remove the , from 7,837.52- then the formula give me the correct value.

I would like a macro that will format the data that contains a trailing minus in col K correctly

For eg 7,837.52- must be formated as (7 837.52)

See sample data below

Excel Workbook
	K	L	M
63	7,837.52-	24	#VALUE!
64	1,381.68-	24	#VALUE!
65	1 389.55	25-	1 389.55
Sheet1

 

[jmthompson]

=substitute(if(right(a1,1)="-",concatenate("(",substitute(a1,"-",""),")"),a1),","," ")

 

[howard]

Hi Jen

Thanks for the reply. Your formula is formatting the data correctly. However, when I try to sum the data in Col M , it gives me 0.00

It would be appreciated if you could assist me

Excel Workbook
	K	L	M
63	7,837.52-	24	(7 837.52)
64	1,381.68-	24	(1 381.68)
65	1 389.55	25-	1389.55
Sheet1

 

[Oaktree]

=if(right(k63)="-",-substitute(k63,"-",""),k63)

 

[jmthompson]

That formula converted to text. Try this formula

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

With this custom format

# ##0.00;(# ##0.00)

 

[howard]

Hi Jen

Thanks for the help, much appreciated

Regards

Howard