Removing leading/trailing spaces




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? :)



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
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.


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.)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question