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

--

"Niniel" <(E-Mail Removed)> wrote in message
news:FD4F2159-02EC-4556-9C4C-(E-Mail Removed)...
> 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.
>
>

TRIM function?

"Niniel" wrote:

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

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

"PCLIVE" wrote:

> 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
>
> --
>
> "Niniel" <(E-Mail Removed)> wrote in message
> news:FD4F2159-02EC-4556-9C4C-(E-Mail Removed)...
> > 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.
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:45E203D9-AEC4-481D-BA09-(E-Mail Removed)...
> Would this also work for the entire worksheet? I don't want to have to
> specify a range.
>
> "PCLIVE" wrote:
>
>> 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
>>
>> --
>>
>> "Niniel" <(E-Mail Removed)> wrote in message
>> news:FD4F2159-02EC-4556-9C4C-(E-Mail Removed)...
>> > 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.
>> >
>> >
>>
>>
>>

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

On Mon, 20 Aug 2007 14:38:20 -0700, Niniel <(E-Mail Removed)>
wrote:

>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

You might try clean instead of trim.

--

"Niniel" <(E-Mail Removed)> wrote in message
news:34C22504-CD8C-48BA-88D4-(E-Mail Removed)...
> 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

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

"PCLIVE" wrote:

> You might try clean instead of trim.

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

On Aug 20, 11:10 pm, Niniel <Nin...@discussions.microsoft.com> wrote:
> No, I think Trim is exactly what I need, I just have to get it to work in my
> macro.
>
>
>
> "PCLIVE" wrote:
> > You might try clean instead of trim.- Hide quoted text -
>
> - Show quoted text -