Remove Spaces After Number
 

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

Answer: Remove Spaces After Number
 

Hi Hardeep,

I can definitely help you with that! It sounds like you have some extra spaces after your numbers in your Excel sheet, which is causing issues with your sum function. Here's how you can remove those spaces:

1. Select the column that contains the numbers with spaces.
2. Click on the "Data" tab in the ribbon at the top of the screen.
3. Click on "Text to Columns" in the "Data Tools" section.
4. In the "Convert Text to Columns Wizard" that pops up, select "Delimited" and click "Next."
5. In the next screen, make sure only "Space" is selected as the delimiter and click "Next."
6. In the final screen, make sure "General" is selected as the column data format and click "Finish."

This should remove any extra spaces from your numbers and allow you to use the sum function properly. Let me know if you have any other questions or if this solution worked for you!

Remove Spaces After Number
 

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


Pai wrote:

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

Remove Spaces After Number
 

Thanks Dave

Work perfectly

Thanks Again

"Dave Peterson" wrote:

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


Pai wrote:

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

Excellent tip ! Did work perfectly ! Let me translate into Brazilian Portuguese !

Dica excelente ! Remove os espaços em branco após o último caracter da célula do Excel !

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

Remover espaços em branco na célula do Excel

Makoto Shimizu