how can i trim these values in a loop?

551569206 024201036
249721825 019

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" wrote:

how can i trim these values in a loop?

551569206 024201036
249721825 019

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

"Mike" wrote:

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" wrote:

how can i trim these values in a loop?

551569206 024201036
249721825 019

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

"Mike" wrote:

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

"Mike" wrote:

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" wrote:

how can i trim these values in a loop?

551569206 024201036
249721825 019

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 wrote:

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

"Mike" wrote:

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" wrote:

how can i trim these values in a loop?

551569206 024201036
249721825 019

--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

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

"Mike" wrote:

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" wrote:

how can i trim these values in a loop?

551569206 024201036
249721825 019

--

Dave Peterson

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" wrote:

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 wrote:

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

"Mike" wrote:

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" wrote:

how can i trim these values in a loop?

551569206 024201036
249721825 019

--

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

Mike wrote:

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" wrote:

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 wrote:

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

"Mike" wrote:

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" wrote:

how can i trim these values in a loop?

551569206 024201036
249721825 019

--

Dave Peterson


--

Dave Peterson