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


I got it to run without errors, though it's not deleting anything. Th
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

--
Odysseu
-----------------------------------------------------------------------
Odysseus's Profile: http://www.excelforum.com/member.php...fo&userid=1456
View this thread: http://www.excelforum.com/showthread.php?threadid=27412

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

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