Forum Discussion
WorksheetFunction.Substitute fails on some cells but works on other cells in a column
You have attached a .xlsx workbook, so there is no macro.
If you set the horizontal alignment of column A to General, you'll see that some values are left-aligned (so they are text) and others are right-aligned (they are numbers). I suspect that this causes your problem.
Thank you for your reply.
I did not attach a .xlsm file for security reason (I didn't know if I could). Please see the new .xlsm file I'm attaching with the macro.
I suspected some values could be text so I tried converting them, but still can't get all the cells to work.
Please note that I'm converting column "A" and column "D" serves as a back up of the original data.
This i the result when I run your macro using my default system settings (decimal separator = point).
Note that there are no points in column A anymore.
But if I set the decimal separator to comma, this is the result:
Note that several cells still contain a point, for example A3 and A4.
This is because those cells originally contained a number value, and the point is the thousands separator. Excel does not see the thousands separator as part of the value - it is merely part of the display. So that point is not replaced with a comma.
What is your decimal separator?
And can you provide some examples of the desired result?
I'm using Excel 16.47.1 on mac OS Catalina 10.15.7 in Italian. i.e European regional settings, where
we use "." as thousand separator and "," as decimal separator. I did not find any option to change
the separator type in Excel, it seems I have to change the Mac's regional settings and use Excel in English
to change how the separators behave, but that will mess up all my other Excel files.
The data I work on is downloaded from a bank in csv format then brought into the my workbook as query table.
Please see the attached file to see my results when I run the macro with my default European settings.
Column "A" has the results after running the macro, column "B" has my desired results and column "D" has the original data.
Column "A" shows some numbers stored as text even though I tried to convert them to numbers in my macro ?!?
How should I deal with the cells where, as you say, the point is not recognized ?
Thank you.
I'd change the regional settings to use point as decimal separator and comma as thousands separator.
Open the .csv file, then return the regional settings to their original values.