Aug 25 2022 02:24 AM
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
Aug 25 2022 02:37 AM
@Jyggalag77 Choose a regional setting in Windows that uses the separators as desired or override them in Excel as described in the attached link.
Aug 25 2022 02:44 AM
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:
Aug 25 2022 03:07 AM - edited Aug 25 2022 03:08 AM
@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.
Aug 25 2022 04:14 AM
SolutionAug 25 2022 04:33 AM - edited Aug 25 2022 04:34 AM
@Riny_van_Eekelen 's solution of removing the thousands separator (rather than replacing them) is more straightforward and returns the same values.
Aug 25 2022 04:59 AM
@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.
Aug 25 2022 04:14 AM
Solution