Remove Spaces After Number

[Pai]

I have copy some data from a website. And copy to Excel Sheet.

Now i trying to Sum the total of column but neither Autosum nor Sum Function
Work.

There are Space after the Numbers. I tried Trim Function but it is not
Working.

=trim(A1)

Anybody can help me

Hardeep kanwar

 

[Dave Peterson]

=trim() will return text.
=--trim() will coerce things that look like numbers to numbers.

But there are other "white space characters" that aren't really spaces:

=--trim(substitute(a1,char(160),""))

=char(160)
is that HTML non-breaking space.

But instead of using formulas, maybe you could:
select the range to fix
edit|replace:
what: (space character)
with: (leave blank)
replace all

and to fix the HTML non-breaking character:
select the range to fix
edit|replace:
what: alt-0160
with: (leave blank)
replace all

hit and hold the alt key while you type 0160 on the numeric key pad.

 

[Hardeep kanwar]

Thanks Dave

Work perfectly

Thanks Again

=trim() will return text.
=--trim() will coerce things that look like numbers to numbers.

But there are other "white space characters" that aren't really spaces:

=--trim(substitute(a1,char(160),""))

=char(160)
is that HTML non-breaking space.

But instead of using formulas, maybe you could:
select the range to fix
edit|replace:
what: (space character)
with: (leave blank)
replace all

and to fix the HTML non-breaking character:
select the range to fix
edit|replace:
what: alt-0160
with: (leave blank)
replace all

hit and hold the alt key while you type 0160 on the numeric key pad.