Extra Spaces with copied data

[shane24]

I recently had to copy a large amount of data from my company's website. When I copied it over to Excel all cells had trailing spaces, 5 spaces to be exact.

What I need to know is how do I sweep through and get rid of all those extra spaces. They are interferring with calculations and sorting of numbers.

Thanks for all your help.

[dminkov]

Try using function TRIM. It removes all extra spaces at the beginning and at the end of cells, as well as between symbols, if spaces are more than one.

[shane24]

Let me try to show you what I did and see what you think. I am using the quotes so it is easy to see the spaces that are actually in the cell.

Cell A1 = "John Smith "

Using your suggestion of TRIM I did this

Cell B1 = TRIM(B1)

Then in C1 I used the paste special function to paste just the value of the result of B1. I still got "John Smith " as the result.

Am I using TRIM wrong??

[Stefi]

If you have a constant 5 spaces in all cells, you are lucky because you can
replace them with an empty string:
Select all cells (click on the top left corner)
Edit/Replace, type five spaces into field Find and nothing in field Replace,
then click Replace All

Regards,
Stefi


„shane24” ezt �*rta:

>
> I recently had to copy a large amount of data from my company's website.
> When I copied it over to Excel all cells had trailing spaces, 5 spaces
> to be exact.
>
> What I need to know is how do I sweep through and get rid of all those
> extra spaces. They are interferring with calculations and sorting of
> numbers.
>
> Thanks for all your help.
>
>
> --
> shane24
> ------------------------------------------------------------------------
> shane24's Profile: http://www.excelforum.com/member.php...fo&userid=4770
> View this thread: http://www.excelforum.com/showthread...hreadid=490089
>
>

[shane24]

Thank you Stefi.

I tried that and Excel said it couldn't find the data I have searched for.

Any other ideas please?

Thanks,
Shane

[Ron Coderre]

The blank spaces may not really be "spaces". They may be HTML non-breaking spaces.

To remove them:
Edit>Replace
Find What: [alt]+0160 <-Hold down the [Alt] key..press 0160...release [Alt]
Replace with: (leave this blank)
Click [Replace All]

Does that help?

Ron