deleting space in cell behind text

[RickJB]

I have a list over data of over 2000 rows per worksheet.
Now I have to sort this data.
However, because in a large number of the cells which are used to sort the
data, behind the text is "space", these rows are not being sorted.
How can the space in the cell behind the text be deleted all at once.
So it does not have to be deleted one cell at the time.
to note: the space behind the text is not present in all the cells.

 

[Pete_UK]

Put this in a helper column:

=TRIM(A1)

and copy down - this will remove all leading and trailing spaces, as
well as multiple spaces between words. Then highlight the column,
click <copy> then Edit | Paste Special | Values (check) | OK then
<Esc>. This will have fixed the values, so that you can now copy them
over the original values and delete the helper column.

Sometimes, though, those "spaces" are the non-breaking space character
(160) which TRIM doesn't work on, so to get rid of those highlight the
column, then CTRL-H (Find & Replace) and:

Find what: ALT-0160
Replace with: leave blank
Replace All

ALT-0160 means to hold the Alt key down and type 0160 on the numeric
keypad.

Hope this helps.

Pete