The AutoSum function distorted by hidden signs before the figures in a cell

[aca]

When my file contains cells copied/pasted (from other formats probably), some of those cells have acquired hidden signs or other material, not visually apparent, preceding the numeric value.
In such cases, when my macro orders an implicit AutoSum, the cell is seen as text (not so in explicit additions, which see them as numeric all right).
Insisting on giving a general or numeric format to the cells has no effect.
I have to click within each cell, go all the way to the left of the visible characters, and press Delete To Left; only then disappears the invisible ‘something’ that lurked before the figures.
But doing this on every the faulty cell, one by one, is time-consuming.
Can anybody suggest some code to search for all those cells and perform that deletion?.

[TMS]

Please see the yellow banner at the top of the page.

[Pete_UK]

If you have pasted the data from an HTML source, then it is likely to be the non-breaking space character, which has a code of 160. You can use Find & Replace to get rid of all these in one operation, by selecting the offending cells, then CTRL-H to bring up the F&R dialogue box, and then enter:

Find What: Alt-0160

Replace with: leave blank

Click Replace All

where Alt-0160 means to hold down the Alt key while typing 0160 on the numeric keypad.

Hope this helps.

Pete

[aca]

Thank you, TMS for your reminder.
And thanks, Pete_UK, for your instructions; but they don’t seem to apply at all to my Excel, perhaps because it is a different version (its language is Spanish).
But I’ve come across a very simple solution to my own query. In case you’re interested, it’s this:
- Select a column
- Click on the top label, “Data”
- There, on the function “Text in columns”
- On that dialogue boox, “Apply” or “End”.
And that’s it: all numbers in the column are set to the number format and recognized as such by an implicit AutoSum.
I know the function, “Text in columns”, is meant for further work (dividing the cell contents into several columns, requiring several dialogue boxes for the various options); but just ending it at the first dialogue box, does that (apply the number format to figures).
I do that manually for the columns, one by one, in which I see faulty cells, and these are easy to spot as they are marked with a black top left angle.
ACA