Forum Discussion
Solved
How to conditionally replace a . with a , and reversely in Excel?
Hi all,
I currently have a very silly setup of numbers in my column J - N:
As you can see, my numbers are currently formatted like this:
2,007.30
and when it's not in the thousands, it contains no commas, and are formatted as:
528.13
for example.
I would like to swap out every single "." with a "," and reversely swap out every single "," with a "." all at once.
I have tried to look up formulas online, even VBA code, or tried to format with the dropdown option in "Home" by myself, but nothing seems to work. It is very unfortunate 😞
Does anybody have a nice solution to this issue?
Thank you all!
Kind regards,
Jyggalag
- 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! 🙂
6 Replies
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.
