Trim Function

[Mike]

how can i trim these values in a loop?

551569206 024201036
249721825 019

 

[Mike]

Let me be more clear.
The first set of digets will always be 9 char long and the next set of
digits will start at char 16. Id like to loop thru the column and trim the
spaces in the middle

Row1 551569206 024201036
Row2 249721825 019
Row3 249877803 018012
Row4 249877803 018012012236

 

[Mike]

Nevermind I was having a Brain Freeze. Heres what I have used.
cell.Offset(0, 6).Value = Replace(cell.Offset(0, 6).Value, " ", "")

 

[JLGWhiz]

When you get to my age, you just assume those mental lapses. <g>

 

[Dave Peterson]

or maybe...

with cell.Offset(0, 6)
.Value = trim(.Value)
end with

or

with cell.Offset(0, 6)
.Value = application.trim(.Value)
end with

The difference between VBA's Trim and excel's =trim() is how they treat
consecutive internal spaces. VBA's Trim will leave them as-is. Excel's =trim()
will "trim" them to a single character.

 

[Mike]

Thanks Dave but this wouldn't work for me. I was trying that before I posted
my question. Thanks anyway.

 

[Mike]

And this was leaving a 1 space ?? Who knows.
with cell.Offset(0, 6)
..Value = application.trim(.Value)
end with

But this worked
with cell.Offset(0, 6)
Value = Replace(.Value, " ", "")
end with

 

[Dave Peterson]

Ahhh.

I just reread one of your messasges. I thought that this:

551569206 024201036
249721825 019

Represented 4 different cells. Not 2 cells with embedded spaces.

I'd use the same thing you used to remove those internal spaces--as long as any
of those strings of digits would be longer than 15 digits.

(Fewer than 16 digits and you could select the range and do an edit|Replace to
remove the space character.)