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

Try this

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

--
HTH,
Barb Reinhardt



" wrote:

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

On Tue, 1 Jan 2008 17:41:18 -0800 (PST), wrote:

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

On Jan 2, 7:03*am, Ron Rosenfeld wrote:
On Tue, 1 Jan 2008 17:41:18 -0800 (PST), wrote:
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



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

On Wed, 2 Jan 2008 00:49:31 -0800 (PST), Joe
wrote:

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.

---------------------------------------
For Each c In Range("A1:A500")
c.Value = Trim(c.Value)
Next c
------------------------------------


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

"Ron Rosenfeld" wrote:

On Wed, 2 Jan 2008 00:49:31 -0800 (PST), Joe
wrote:

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.

---------------------------------------
For Each c In Range("A1:A500")
c.Value = Trim(c.Value)
Next c
------------------------------------


--ron

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

On Wed, 2 Jan 2008 06:14:19 -0800 (PST), wrote:

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

On Wed, 2 Jan 2008 05:28:05 -0800, Jim May
wrote:

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

On Wed, 2 Jan 2008 05:28:05 -0800, Jim May
wrote:

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

Thanks for the response.
Jim

"Ron Rosenfeld" wrote:

On Wed, 2 Jan 2008 05:28:05 -0800, Jim May
wrote:

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