Forum Discussion
How to conditionally replace a . with a , and reversely in Excel?
- Hm
I'm not very good at excel so I had some issues with this, although I appreciate it a lot!
In the end my solution was to:
1) CTRL + H the columns and replace , with a random number or text letter, in my case Ø
2) Replace all . with a ,
3) Replace all Ø with a .
Turned out to be quite easy and simple after all! 🙂
Jyggalag77 Then you are dealing with texts. Enter the formula =ISTEXT(ref) somewhere, where ref should be the cell reference containing the "number". TRUE will confirm you are dealing with texts.
Select the columns with the "numbers" and use Find & Replace (Ctrl-H) to replace first the commas with nothing. The find points and replace these with commas.
That should leave you with 2007,30 and it should be recognized as a number. Format as currency and Excel follows your system settings or what you specified in the Excel options.
I'm not very good at excel so I had some issues with this, although I appreciate it a lot!
In the end my solution was to:
1) CTRL + H the columns and replace , with a random number or text letter, in my case Ø
2) Replace all . with a ,
3) Replace all Ø with a .
Turned out to be quite easy and simple after all! 🙂
Jyggalag77 The fact that you could find commas confirms that you are dealing with texts. Unless you have a good reason for continuing with texts that look like numbers, I would recommend to create real numbers from these texts, as described earlier. Calculating with numbers is so much easier than calculating with texts. If you are not intending to do any calculations, I wonder why you chose to work with Excel.
Riny_van_Eekelen 's solution of removing the thousands separator (rather than replacing them) is more straightforward and returns the same values.
