Removing extra space

[DashboardNovice]

In the attached Excel file I have a sample of some data that I copied out of a webpage and pasted into Excel (so I guess there could be hidden HTML characters). See row 4 in yellow.

When I copied and pasted, I noticed that there is an additional space character after the value in each cell. The following do not work:

1. Find (space) and Replace (with blank)
2. The CLEAN function
3. The TRIM function

So I don't know what else to try. Can anybody offer any suggestions?

 

[bobhc]

A much asked question when copying and pasting HTML, but people seem to be very apprehensive of using the search bow.

http://chandoo.org/forum/threads/sum-function-not-working-on-some-cells.17280/#post-104556


.

 

[NARAYANK991]

Hi ,

I had earlier posted this , in response to the post which Bob has referred to :

The Excel help is very informative on this ; just enter the word CLEAN in your Excel help , and go through the results.

I can only repeat it.

If you go through the process , you will see that you need to do a Find and Replace , not by replacing a space character with a blank , since a space character will anyway be eliminated by using the TRIM function.

You need to do a Find and Replace to replace the characters whose ASCII code is 160 , since this character will not be eliminated by either the TRIM or the CLEAN function.

To find out what a character is , you can use the formula :

=CODE(character)

In your worksheet , cell C4 has 2 characters ; you can do =LEN(C4) to see this.

Now , if you use a formula such as :

=CODE(MID(C4,2,1))

you will see the result is 160.

Select the text where you suspect the presence of this character , and do a Find and Replace , replacing all characters with the ASCII code of 160 , by a normal space character. The normal space character can then be removed by the TRIM function.

Narayan

 

[pecoflyer]

pERHAPS =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))