Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading/trailing spaces
Hello,
OK, having split my address column into separate columns for each line, I'm now left with leading spaces in the 2nd and subsequent columns. How do I remove these leading spaces? I've experimented with the =TRIM() function and can happily create a duplicate list on the same worksheet, but that's no good to me - I want the TRIMmed data to be the only data so that I can use it for my mail merge. I can't delete the original data, or move the TRIMmed data, because the =TRIM() function no longer works, of course. There must be some simple way to edit the whole worksheet to remove any leading spaces in the cells, or some way of using =TRIM() to do it. What have I missed this time? :) Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading/trailing spaces
A very simple solution
1 - copy the worksheet to a new worksheet 2 - for the cells in the new worksheet, use the formula =TRIM(sheet1!B2) and drag it across and down 3 - select all the cells in the new worksheet (use the little grey box above row 1 and to the left of column A) where the cursor turns into a big white cross 4 - copy 5 - Edit Paste Special Values now you have a version of your sheet with just the TRIMed stuff in it. You can paste it back over the original sheet if you want. -- Allllen "Chuda" wrote: Hello, OK, having split my address column into separate columns for each line, I'm now left with leading spaces in the 2nd and subsequent columns. How do I remove these leading spaces? I've experimented with the =TRIM() function and can happily create a duplicate list on the same worksheet, but that's no good to me - I want the TRIMmed data to be the only data so that I can use it for my mail merge. I can't delete the original data, or move the TRIMmed data, because the =TRIM() function no longer works, of course. There must be some simple way to edit the whole worksheet to remove any leading spaces in the cells, or some way of using =TRIM() to do it. What have I missed this time? :) Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading/trailing spaces
You can copy the column of trimmed data and then 'paste special'...'values'
right over top of your original row. (Obviously always make sure your file is backed up elsewhere before deleteing original data from your sheet.) "Chuda" wrote: Hello, OK, having split my address column into separate columns for each line, I'm now left with leading spaces in the 2nd and subsequent columns. How do I remove these leading spaces? I've experimented with the =TRIM() function and can happily create a duplicate list on the same worksheet, but that's no good to me - I want the TRIMmed data to be the only data so that I can use it for my mail merge. I can't delete the original data, or move the TRIMmed data, because the =TRIM() function no longer works, of course. There must be some simple way to edit the whole worksheet to remove any leading spaces in the cells, or some way of using =TRIM() to do it. What have I missed this time? :) Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How keep Leading/Trailing spaces when save in Excel? | Excel Discussion (Misc queries) | |||
Removing spaces from columns | Excel Worksheet Functions | |||
Removing 2 extra spaces in front of dates in imported excel doc | Excel Discussion (Misc queries) | |||
Removing spaces in cells with data in it | Excel Discussion (Misc queries) | |||
Removing trailing spaces from cells ? | Excel Worksheet Functions |