Delete space after word

[Niniel]

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.

 

[PCLIVE]

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

 

[Toppers]

TRIM function?

 

[Niniel]

Would this also work for the entire worksheet? I don't want to have to
specify a range.

 

[PCLIVE]

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

--

 

[Niniel]

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

 

[Gord Dibben]

Or use ActiveSheet.UsedRange which in most cases will not include the entire
worksheet.


Gord Dibben MS Excel MVP

 

[PCLIVE]

You might try clean instead of trim.

 

[Niniel]

No, I think Trim is exactly what I need, I just have to get it to work in my
macro.

 

[Pete_UK]

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

 

[Niniel]

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.

 

[Niniel]

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.

 

[PCLIVE]

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

 

[Niniel]

I found a nice commercial, yet free for private and commercial use, add-in
that does the job. It's not super-fast, but I guess as fast as one can
expect, especially when I just use Ctrl-A for the cells that actually need
checking. And it has a nice progress bar.

http://www.ablebits.com/excel-trim-spaces/index.php

 

[E F]

Thanks to all of the people who posted on this topic. I was able to successfully remove all of the extra spaces after the last word in a cell by using the TRIM function. The steps I used can be found below.

1. Make a copy of the Excel file you are working on so that you have a backup in case something goes wrong.
2. Select all of the cells in the column you want to "clean".
3. Copy these cells and paste them into Column A of a NEW Excel document.
4. In Column B, Cell 1, type ?=TRIM(A1)? and press Enter. This will remove any extra spaces at the end of the last word in Cell A1.
5. Copy this formula all the way down Column B so that this process is applied to every cell in Column A.
6. Copy all of the entries in Column B (currently formulas and not actual values)
7. Paste this information into Column C.
8. Choose the option to paste ?Values Only?. This will paste all of the terms as actual values instead of formulas, but will paste them with all of the spaces removed after the last word in the term entry.
9. Now you can copy all of the ?cleaned? entries from Column C back into your original Excel file, overwriting the cells that you wanted to clean.

MAKE SURE everything has copied and pasted correctly. Make sure all of the cells are still in the same order and that no terms have been added or removed. If even one term is out of order, all of the term entries after this incorrectly entered term will be aligned improperly with their adjacent cells.

I hope this helps!

-EF