Delete Part of Cell?

[Odysseus]

Hi, I've been landed with a rather large project to re-write som
spreadsheets for my companies reports due to a new database... oh th
joys. I have no VBA experience and some basic macro usage. I've got 2
days to re-write about 10 worksheets so I may be popping in from tim
to time to ask for some help.

My first question for today.

Can you delete part off a cell using a macro / VBA.

Example Cell from column (cost centre) :-

NTL089 N

I need to get rid of the N at the end, this could also be a Y. (yes o
no) This is used by certain departments for audit purposes so I can'
take it out of the DB export. Extra problems are that some may hav
neither and some of the cost centres end in a Y or a N.

So the Macro needs to do a

If M column cells have a N or Y to the right preceded by 2 or mor
spaces delete Y or N if not leave.

Does that make sense? Is it possible?

Thank

 

[Guest]

Hi
try something like

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

 

[JulieD]

Hi Odysseus

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

Please feel free to post back if you need help "installing" it.

Hope this helps
Regards
JulieD

 

[Ramthebuffs]

Maybe somebody can help with my very similar problem. I'm trying t
remove the left part of a cell and have tried to adjust the above code
but can't get it to work.

On some of my cells it begins with a- or b- with 3 spaces before this
So its <space><space><space>a-

Here is what I tried. Any thoughts

For Each cell In Range("team1batting!A2:A25")
If cell.Value = "" Then Exit Sub
sStr = Trim(cell.Value)
If Left(sStr, 3) = " a-" Or Left(sStr, 3) = " b-" Then
cell.Value = Trim(Right(sStr, Len(sStr) - 5))
End If
Nex

 

[Roman]

You need to place the three spaces into the tested string. Not " a-"
but " a-"

This should work for you:

For Each cell In Range("team1batting!A2:A25")
If cell.Value = "" Then Exit Sub
sStr = Trim(cell.Value)
If Left(sStr, 3) = " a-" Or Left(sStr, 3) = " b-" Then
cell.Value = Trim(Right(sStr, Len(sStr) - 5))
End If
Next

 

[Roman]

Hi Ramthebuffs,
you need to place the three spaces into the tested string. Not " a-"
but " a-"