Delete Part of Cell?
 

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

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

Delete Part of Cell?
 

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



"Odysseus" wrote:


Hi, I've been landed with a rather large project to re-write some
spreadsheets for my companies reports due to a new database... oh the
joys. I have no VBA experience and some basic macro usage. I've got 28
days to re-write about 10 worksheets so I may be popping in from time
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 or
no) This is used by certain departments for audit purposes so I can't
take it out of the DB export. Extra problems are that some may have
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 more
spaces delete Y or N if not leave.

Does that make sense? Is it possible?

Thanks


--
Odysseus
------------------------------------------------------------------------
Odysseus's Profile: http://www.excelforum.com/member.php...o&userid=14563
View this thread: http://www.excelforum.com/showthread...hreadid=274129

Delete Part of Cell?
 

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


"Odysseus" wrote in message
...

Hi, I've been landed with a rather large project to re-write some
spreadsheets for my companies reports due to a new database... oh the
joys. I have no VBA experience and some basic macro usage. I've got 28
days to re-write about 10 worksheets so I may be popping in from time
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 or
no) This is used by certain departments for audit purposes so I can't
take it out of the DB export. Extra problems are that some may have
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 more
spaces delete Y or N if not leave.

Does that make sense? Is it possible?

Thanks


--
Odysseus
------------------------------------------------------------------------
Odysseus's Profile:
http://www.excelforum.com/member.php...o&userid=14563
View this thread: http://www.excelforum.com/showthread...hreadid=274129

Delete Part of Cell?
 

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

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

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

Delete Part of Cell?
 

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

Delete Part of Cell?
 

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