Delete Part of Cell?

[Odysseus]

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

 

[Tom Ogilvy]

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)

 

[Tom Ogilvy]

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

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)