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 Choose a regional setting in Windows that uses the separators as desired or override them in Excel as described in the attached link.
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! 🙂