Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting last letter if a cell
What I'd like to do is, if the last letter of what is inputed into a
cell is the "+" symbol. Then delete it. I have a cell defined by: Sheet12.Cells(11, 2) = d & "+" + l & "+" + w & "+" + s Here's what i get fromt that cell. ..5D + 2L + 3W + 2S if i change it and s = 0 i get ..5D + 2L + 3W + i want to get ..5D + 2L + 3W So i was hoping maybe there was a command, to delete a space off the end of a cell using and IF statement. IF last letter is a "+" , delete 1-2 spaces of the end of the cell. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting last letter if a cell
Suppose to be titled
"Deleting last letter OF a cell" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting last letter if a cell
dim myStr as string
mystr = ".5D + 2L + 3W + " 'extra spaces, too mystr = trim(mystr) 'gets rid of leading and trailing spaces if right(mystr,1)="+" then mystr = trim(left(mystr,len(mystr)-1)) 'also cleans up trailing spaces with the trim. end if msgbox "***" & mystr & "***" Kevin O'Neill wrote: What I'd like to do is, if the last letter of what is inputed into a cell is the "+" symbol. Then delete it. I have a cell defined by: Sheet12.Cells(11, 2) = d & "+" + l & "+" + w & "+" + s Here's what i get fromt that cell. .5D + 2L + 3W + 2S if i change it and s = 0 i get .5D + 2L + 3W + i want to get .5D + 2L + 3W So i was hoping maybe there was a command, to delete a space off the end of a cell using and IF statement. IF last letter is a "+" , delete 1-2 spaces of the end of the cell. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting last letter if a cell
right click on the sheet tab and select view code
in the left dropdown at the top of the resulting module, select WorkSheet and in the right dropdown select Change (not SelectionChange) then put code in this sub (the change event) like this Dim s as String If Target.count 1 then exit sub if isempty(target) then exit sub if Target.Column = 2 then s = Trim(Target.Value) if Right(s,1) = "+" then Application.enablevents = False Target.Formula = Trim(Left(s,len(s)-1)) Application.EnableEvents = True end if end if as written works only in column B. Change to suit -- Regards, Tom Ogilvy "Kevin O'Neill" wrote in message ups.com... Suppose to be titled "Deleting last letter OF a cell" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting last letter if a cell
Ah Aswome guys got that working good. There's that other tricky part,
which I think will actually be tricky. If my result is: ..5D + .5L + 2S convert that to: ..5(D+L) +2S What do you think? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting all data in cell if value starts with 3 particular letter | Excel Discussion (Misc queries) | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
if a cell = a particular letter or even contains that letter | Excel Worksheet Functions | |||
press letter and go 2 entry begin w letter in data validation drop | Excel Programming |