Delete Part of Cell?

  • Thread starter Thread starter Odysseus
  • Start date Start date
O

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
 
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
 
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
 
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
 
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
 
Hi Ramthebuffs,
you need to place the three spaces into the tested string. Not " a-"
but " a-"
 
Back
Top