Remove Spaces After Number

P

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
 
D

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.
 
H

Hardeep kanwar

Thanks Dave

Work perfectly

Thanks Again

Dave Peterson said:
=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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top