Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove a space infront of text in a cell? | Excel Discussion (Misc queries) | |||
deleting unused space at the end of a worksheet | Excel Worksheet Functions | |||
Location of space in a text in a cell | Excel Worksheet Functions | |||
Deleting a space between a group of Numbers & Letters in a cell | New Users to Excel | |||
Function to remove a space from text in cell WITHOUT macro?? | Excel Worksheet Functions |