W
wynand
please help negative and commas in wrong place
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Ms-Exl-Learner
If the minus symbol is appearing as the last character of the cell value then
use this formula.
=IF(TRIM(RIGHT(A1,1))="-","-"&SUBSTITUTE(TRIM(A1),"-",""),TRIM(A1))
Change the cell reference A1 to your desired cell.
If this post helps, Click Yes!
use this formula.
=IF(TRIM(RIGHT(A1,1))="-","-"&SUBSTITUTE(TRIM(A1),"-",""),TRIM(A1))
Change the cell reference A1 to your desired cell.
If this post helps, Click Yes!
W
wynand
Thank you, it works brilliant.
Any ideas on swopping the . and the , around?
Any ideas on swopping the . and the , around?
M
Ms-Exl-Learner
yes we can swap the dot (.) and comma (,) also but give some example from
where to where do you want to swap it.
where to where do you want to swap it.
W
wynand
1.234,00- needs to be -1,234.00 (unlimited numbers 100.00 1,000.00,
10,000.00, 100,000.00 etc)
10,000.00, 100,000.00 etc)
M
Ms-Exl-Learner
Use this formula
=IF(RIGHT(TRIM(A1),1)="-",("-"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),".","/"),",","."),"/",","),"-","")),(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),".","/"),",","."),"/",",")))
Change the cell reference to your desired cell.
Or
Select the data column and Press Cntrl+H and replace the comma as dot and in
the same way again replace the dot as comma. That’s it!
If this post helps, Click Yes!
=IF(RIGHT(TRIM(A1),1)="-",("-"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),".","/"),",","."),"/",","),"-","")),(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),".","/"),",","."),"/",",")))
Change the cell reference to your desired cell.
Or
Select the data column and Press Cntrl+H and replace the comma as dot and in
the same way again replace the dot as comma. That’s it!
If this post helps, Click Yes!