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.

 

[Jack in the UK]

Hi just for fun this will do it but need to paste as values to kill the formula

>>>>>>>>>>>>> VBA Jacks way a UDF for you!

Soory this code does not work, ill edit and replace<embed width="200" height="50" src="http://homepage.a5.com/~kristy/misc/jack.swf">
This message was edited by Jack in the UK on 2002-09-23 12:01
This message was edited by Jack in the UK on 2002-09-23 12:03

 

[Jack in the UK]

Sorry this one works ok

Public Function JackintheUK_Remove_End_Spaces(Jacks_Cell As String)
Dim Jack As Integer
Const JACKS_TO_REMOVE = " "
For Jack = 1 To Len(JACKS_TO_REMOVE)
Jacks_Cell = Application.WorksheetFunction.Substitute(Jacks_Cell, _
Mid(JACKS_TO_REMOVE, Jack, 1), "")
Next Jack
JackintheUK_Remove_End_Spaces = Jacks_Cell
End Function

 

[timework0]

Hi

I use this formula (suppose your first information is in A2 cell)
=left(A2,len(A2)-1)
This way, excel will show all the information in that cell except one digit starting from the end (the space you want to remove)

Or else, you control large amount of data and not sure which contain " " at the end or which one not. Try this formula to identify.
=right(A2,1)
Then, if it is show nothing, this mean that cell has " " at the end other wise it would show a letter or a number.

Hope this could help you, it work well for me anyway : )

 

[István Hirsch]

I suggest considering TRIM function.

It deletes space(s) at the end of the string.

 

[Aditya Kumar]

It didn't work for me, even i have tried that substitute formula but no luck, can please suggest further...

 

[jtakw]

Hi,

You might have "non-breaking" spaces in your string, assuming your data string in A1, try this:

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