Macro for removing end space on each cell

[Vijayonline2008]

I'am attaching here a small portion which I'am working on. Since it is copied from an HTML file it has a space at the end of each value on the cell.

Is there any macro to remove the space as I'am not able to do any multiplication or subtraction as the value is not treated as numeric.

 

[Chihiro]

Why not import from HTML file instead of copy and paste?

Otherwise, if you need to clean number string only. You can use code in link below.

http://chandoo.org/forum/threads/convert-text-to-numbers.28395/#post-169971

 

[narunfca]

Select all cells. Goto Find and Replace dialog box by press Ctrl+H.
Type a spacebar in the Find what box and press Alt+A to remove all the spaces.

with best regards

Arun N

 

[Chihiro]

@narunfca
OP's string contains not space. But a non-breaking-space character (CHAR(160)), which is extensively used in the web.

To replace it use UDF mentioned above, or you can replace CHAR(160) with "".
Something like...
=SUBSTITUTE(A2,CHAR(160),"")+0

Edit: Or type =CHAR(160) in out of way cell. Press F9 and copy without leaving the cell. Use CTRL + F, and look in values and replace.