In column M of Sheet1 I had
fkaldfjk N
djklfj Y
skjf N
sdkfjkN
skdjf;Y
dkfjk Y
dkslfj; N
I ran the code as posted
it produced
fkaldfjk
djklfj
skjf
sdkfjkN
skdjf;Y
dkfjk
dkslfj;
The code as written works on Column M, Sheet1 - is that where your data is?
are there at least two spaces preceding the Y or N to be removed (that was
your specificiation).
Anyway, the code works against the situation described.
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
Sounds like there are spaces after the N or Y
Sub removeNY()
Dim sStr as String, cell as Range
For Each cell In Range("Sheet1!M:M")
If cell.Value = "" Then Exit Sub
sStr = Trim(Cell.Value)
If Right(sStr, 3) = " Y" Or Right(sStr, 3) = " N" Then
cell.Value = Left(sStr, Len(sStr) - 1)
End If
Next
End Sub
to get rid of the trailing spaces left by removing the Y or N change
cell.Value = Left(sStr, Len(sStr) - 1)
to
cell.Value = Trim(Left(sStr, Len(sStr) - 1))
If the above code doesn't work, then does the data come from a web page.
There is a possibility the spaces are actually chr(160) vice chr(32).
Trim
does not work well with chr(160). if so you could do
sStr = Trim(Application.substitute(Cell.Value,chr(160)," "))
in lieu of
sStr = Trim(Cell.Value)
--
Regards,
Tom Ogilvy
"Odysseus" wrote in message
...
Hi Julie,
I tried your
Code:
--------------------
Sub removeNY()
For Each cell In Range("Sheet1!M:M")
If cell.Value = "" Then Exit Sub
If Right(cell, 3) = " Y" Or Right(cell, 3) = " N" Then
cell.Value = Left(cell, Len(cell) - 1)
End If
Next
End Sub
--------------------
I got it to run without errors, though it's not deleting anything. The
cells stay the same 
Any Ideas?
Frank I tried yours
Code:
--------------------
sub foo()
dim rng as range
dim cell as range
Dim res
set rng=selection
for each cell in rng
res=application.trim(cell.value)
if right(res,1)="N") or right(res,1)="Y" then
cell.value=left(res,len(res)-1)
end if
next
end sub
--------------------
An got nowhere, sorry :( couln't get it to run at all .
--
Odysseus
------------------------------------------------------------------------
Odysseus's Profile:
http://www.excelforum.com/member.php...o&userid=14563
View this thread:
http://www.excelforum.com/showthread...hreadid=274129