Trimming Characters

[james.y.so]

Hi there,

I've downloaded some data in excel but with an unwanted character both
at the front and the end of each of the cell. Please help with a macro
to remove both the first and the last characters of the the cells from
say A1:A500. Thanks in advance.

James

 

[Barb Reinhardt]

Try this

=mid(a1,2,len(a1)-2)

 

[Ron Rosenfeld]

Hi there,

I've downloaded some data in excel but with an unwanted character both
at the front and the end of each of the cell. Please help with a macro
to remove both the first and the last characters of the the cells from
say A1:A500. Thanks in advance.

James

See if this does what you want:

==========================================
Option Explicit
Sub TrimEnds()
'Backup your data first, as this Sub has no way of
'knowing if it has already run once
Dim c As Range
For Each c In Range("A1:A500")
If Len(c.Value) > 2 Then
c.Value = Mid(c.Value, 2, Len(c.Value) - 2)
End If
Next c
End Sub
=================================
--ron

 

[Joe]

See if this does what you want:

==========================================
Option Explicit
Sub TrimEnds()
'Backup your data first, as this Sub has no way of
'knowing if it has already run once
Dim c As Range
For Each c In Range("A1:A500")
    If Len(c.Value) > 2 Then
        c.Value = Mid(c.Value, 2, Len(c.Value) - 2)
    End If
Next c
End Sub
=================================
--ron

An extension of the code from Ron...

===================================
Option Explicit
Sub TrimEnds()
'Removes any no of spaces from Front and back

Dim c As Range

For Each c In Range("A1:A500")
Do While left(c,1)<>" "
c=right(c,len(c)-1)
Loop
Do While right(c,1)<>" "
c=left(c,len(c)-1)
Loop
Next c

End Sub
====================================

Joe

 

[Ron Rosenfeld]

An extension of the code from Ron...

===================================
Option Explicit
Sub TrimEnds()
'Removes any no of spaces from Front and back

Dim c As Range

For Each c In Range("A1:A500")
Do While left(c,1)<>" "
c=right(c,len(c)-1)
Loop
Do While right(c,1)<>" "
c=left(c,len(c)-1)
Loop
Next c

End Sub
====================================

Joe,

If all the OP wanted to do was remove <spaces> from the beginning and end, then
it would be much simpler to just use the VBA Trim function.

 

[Jim May]

Ron,
Just wondering here, but when OP states he has "unwanted characters" --
might not these characters be of the "print-type" (I've seen the use of the
Alt-numpad action to remedy,,??)... Anyway, perhaps all recommendations
INCLUDE removing such characters (print-type).. dunno? Is this a legitimate
q?
Thanks for your input and HAPYY NEW YEAR !!
Jim May

 

[james.y.so]

hi there,

thanks everyone for the help.

Just to update, i tried Ron's method and it worked well. The
characters i wanted to remove is a kind of symbol, not from the ASCII
characters i think. It cannot be copied nor pasted.

Anyway the job's done and thanks!

James

 

[Ron Rosenfeld]

hi there,

thanks everyone for the help.

Just to update, i tried Ron's method and it worked well. The
characters i wanted to remove is a kind of symbol, not from the ASCII
characters i think. It cannot be copied nor pasted.

Anyway the job's done and thanks!

James

You're very welcome. Thanks for the feedback
--ron

 

[Ron Rosenfeld]

Ron,
Just wondering here, but when OP states he has "unwanted characters" --
might not these characters be of the "print-type" (I've seen the use of the
Alt-numpad action to remedy,,??)... Anyway, perhaps all recommendations
INCLUDE removing such characters (print-type).. dunno? Is this a legitimate
q?
Thanks for your input and HAPYY NEW YEAR !!
Jim May

The most common character of that type is a nbsp ( <alt-0160> ). It is most
frequently a consequence of copying data from a web page or other html
document. The nbsp is usually used to provide a little margin at the end (or
beginning) of a line.

The solution I gave will remove that.

The VBA Trim and also Joe's version will only remove ordinary <space>'s.

Best wishes,
--ron

 

[Ron Rosenfeld]

Ron,
Just wondering here, but when OP states he has "unwanted characters" --
might not these characters be of the "print-type" (I've seen the use of the
Alt-numpad action to remedy,,??)... Anyway, perhaps all recommendations
INCLUDE removing such characters (print-type).. dunno? Is this a legitimate
q?
Thanks for your input and HAPYY NEW YEAR !!
Jim May

Hit <enter> too quickly.

Barb's solution will also work, but the OP specifically asked for a macro.
--ron

 

[Jim May]

Thanks for the response.
Jim

The most common character of that type is a nbsp ( <alt-0160> ). It is most
frequently a consequence of copying data from a web page or other html
document. The nbsp is usually used to provide a little margin at the end (or
beginning) of a line.

The solution I gave will remove that.

The VBA Trim and also Joe's version will only remove ordinary <space>'s.

Best wishes,
--ron