Trimming Characters

  • Thread starter Thread starter james.y.so
  • Start date Start date
J

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
 
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
Click to expand...

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
 
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
Click to expand...



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
 
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
====================================
Click to expand...

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.
 
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
 
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
 
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
Click to expand...

You're very welcome. Thanks for the feedback
--ron
 
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
Click to expand...

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,
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
Click to expand...

Hit <enter> too quickly.

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

Ron Rosenfeld said:
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
Click to expand...
 
Back
Top