Delete space after word

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I would like to make a macro that checks a worksheet and deletes any space
it finds at the end of a word in a cell, but only if it's the last word. In
other words "test " is what I'd like to fix, but "test test" or "test test
test" (etc) is not.

How could this be accomplished?

Thank you.
 
One way:

For Each cell In Range("A1:A10")
cell.Value = Trim(cell.Value)
Next cell


Adjust your range as needed.

You could also do this via a formula.

With data in A1.
=TRIM(A1)
Then copy down as needed.
Copy entire column and Paste Special-Values only.

HTH,
Paul
 
Yes, it would...but I wouldn't suspect that you'd want to bogg down your
system by checking of the 168,099,840 cells (minimum depending on excel
version). Find the column and row at which your data stops, and adjust your
range accordingly. You should be able to do this by pressing Ctrl+End.

Regards,
Paul

--
 
Ok. Since the number of rows isn't constant, I figured I'd specify a very
generous range, say 2k rows, but that didn't work. I saw activity (the cursor
flickered for a couple of seconds), but the trailing spaces in my 2 test
cells were not deleted.

For Each cell In Range("b4:b2222")
cell.Value = Trim(cell.Value)
Next cell
 
Or use ActiveSheet.UsedRange which in most cases will not include the entire
worksheet.


Gord Dibben MS Excel MVP
 
Note that those "spaces" not removed by TRIM may be the non-breaking
space character (code = 160), rather than a normal space (code = 32).
TRIM will not remove these.

Hope this helps.

Pete
 
Good idea, but I don't think that's the case; those are spaces I inserted
myself for testing purposes by pressing the space bar. It should work. I must
be doing something else wrong.
 
Ok, turns out it was my mistake... specified the range incorrectly - it
should have been bx2222.
So after I changed that I ran my macro and it started working, and it
worked, and worked, and worked... until I stopped it because it's just taking
too long.

Is Trim always a slow function? In that case maybe this isn't what I should
be doing.
 
Yes. That is what I was saying in a previous post that it would "bogg" (sp)
your system down. If it is just one column (bx), then you might try using
the formula method. For example, in an empty column, say BZ, enter this
formula in BZ4:
=TRIM(BX4)

Then copy the formula down through BZ2222. Ensure that your worksheet is
calculated by pressing F9. Then copy BZ4:BZ2222.
Right-click BX4 and select Paste Special. Select 'Values'. Once that is
complete, you can delete your temporary column BZ.

HTH,
Paul
 
Back
Top