| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I convert 1.234,00- to -1,234.00?
please help negative and commas in wrong place
|
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I convert 1.234,00- to -1,234.00?
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! -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: please help negative and commas in wrong place |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I convert 1.234,00- to -1,234.00?
Thank you, it works brilliant.
Any ideas on swopping the . and the , around? "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: please help negative and commas in wrong place |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I convert 1.234,00- to -1,234.00?
yes we can swap the dot (.) and comma (,) also but give some example from
where to where do you want to swap it. -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: Thank you, it works brilliant. Any ideas on swopping the . and the , around? "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: please help negative and commas in wrong place |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I convert 1.234,00- to -1,234.00?
1.234,00- needs to be -1,234.00 (unlimited numbers 100.00 1,000.00,
10,000.00, 100,000.00 etc) "Ms-Exl-Learner" wrote: yes we can swap the dot (.) and comma (,) also but give some example from where to where do you want to swap it. -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: Thank you, it works brilliant. Any ideas on swopping the . and the , around? "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: please help negative and commas in wrong place |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do I convert 1.234,00- to -1,234.00?
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. Thats it! If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: 1.234,00- needs to be -1,234.00 (unlimited numbers 100.00 1,000.00, 10,000.00, 100,000.00 etc) "Ms-Exl-Learner" wrote: yes we can swap the dot (.) and comma (,) also but give some example from where to where do you want to swap it. -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: Thank you, it works brilliant. Any ideas on swopping the . and the , around? "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "wynand" wrote: please help negative and commas in wrong place |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can the "convert" function in excel convert to UK gallons? | Excel Discussion (Misc queries) | |||
| how will i convert 05.01.2007 convert 05.Jan.2007 format? | New Users to Excel | |||
| convert value in word. For Exampe Rs.115.00 convert into word as . | Excel Discussion (Misc queries) | |||
| convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
| Convert Time...!convert tenths of a second | Excel Discussion (Misc queries) | |||
Linear Mode
