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! 🙂
Thank you so much!
I tried this, but it did not work, please see photo below:
I do not think regional setting will help either, not sure? As of now, the value 2,007.30 is not even recognized as a number (even if i change formatting to number as well), because if i write = CELL containing 2,007.30 + 1 it will return #VALUE).
Any other idea? Sorry for making this so difficult, but I would hate to have to edit over 5000 rows manually 😞
Photo:
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.
- 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 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.
