SOLVED

How to conditionally replace a . with a , and reversely in Excel?

Occasional Contributor

Hi all,

I currently have a very silly setup of numbers in my column J - N:

 

excel1.PNG

 

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

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.

https://support.microsoft.com/en-us/office/change-the-character-used-to-separate-thousands-or-decima... 

Hi @Riny_van_Eekelen 

 

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_0-1661420685952.png

 

@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.

 

best response confirmed by Jyggalag77 (Occasional Contributor)
Solution
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 

@Riny_van_Eekelen 's solution of removing the thousands separator (rather than replacing them) is more straightforward and returns the same values. 

@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.