Remove space from end of text cell

[cidgreen]

Hello everyone,

Does anyone know if there is a quick way to remove a space from the end of a text cell?

I have a list of names in a column, such as Real Madrid, Osasuna, Tenerife, Celta Vigo, but some of them have a space after the name which is fouling up my attempts to sort them. Because some of them are two words or more I cannot replace the space with nothing.

There are 18 and a half thousand rows in this file, I'm not just being lazy!

Is there a clever way to remove an unwanted space at the end of a text cell that anyone knows of?

Thanks,
Chris.

 

[Ian Mac]

On 2002-09-23 06:36, cidgreen wrote:
Hello everyone,

Does anyone know if there is a quick way to remove a space from the end of a text cell?

I have a list of names in a column, such as Real Madrid, Osasuna, Tenerife, Celta Vigo, but some of them have a space after the name which is fouling up my attempts to sort them. Because some of them are two words or more I cannot replace the space with nothing.

There are 18 and a half thousand rows in this file, I'm not just being lazy!

Is there a clever way to remove an unwanted space at the end of a text cell that anyone knows of?

Thanks,
Chris.

I think you're being really lazy

but I am to.

In a blank column put:

=TRIM(A1)

where A1 is the start of your data, drag this down the column.
Copy>Paste Special>Values into the orignal column.

TRIM will take out any unwanted spaces, inc. before and after a text string.

Hope this helps,

 

[cidgreen]

Ahhhahaha I knew there would be a way round it - you're a legend!

Thanks very much,
The King of Laziness no wait I mean Chris.

 

[AP]

I think I'm lazier than the both of you, I won't resort to the high level thinking required with formulas, I'd just use find & replace (find..2 spaces of the spacebar, replace nothing)

 

[Ian Mac]

On 2002-09-23 07:54, AP wrote:
I think I'm lazier than the both of you, I won't resort to the high level thinking required with formulas, I'd just use find & replace (find..2 spaces of the spacebar, replace nothing)

This won't solve the problem though, as has been stated in the OP's question:

Is there a clever way to remove an unwanted space at the end of a text cell that anyone knows of?

 

[AP]

Actually, as long as you set it to look for at least 2 blank spaces and not one, it should work.

 

[Ricky Morris]

On 2002-09-23 09:48, AP wrote:
Actually, as long as you set it to look for at least 2 blank spaces and not one, it should work.

Except if there's only one space at the end.

 

[cidgreen]

But TRIM does the job perfectly, so I'm going with that from now on. With a massive list like I have it would take ages doing it the other way (trust me, I got halfway through it before TRIM saved me...)

Cheers,
Chris.

 

[owens_pastor]

another way
=substitute(a1," ","")

 

[kskinne]

the only problem with:

=SUBSTITUTE(A1," ","")

is that it will not only remove the space at the end of the text string, but every space within the text string.

kevin