deleting space in cell behind text
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. |
deleting space in cell behind text
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 On May 15, 12:41*pm, RickJB wrote: 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. |
All times are GMT +1. The time now is 03:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com