Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Part of Cell?
Hi Ramthebuffs,
you need to place the three spaces into the tested string. Not " a-" but " a-" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
Delete part of a cell | Excel Worksheet Functions | |||
delete part of text from a cell | Excel Worksheet Functions | |||
How do I delete part of a text string in every cell it appears in | Excel Discussion (Misc queries) | |||
delete part of a sheet | Excel Programming |