View Full Version : Sleeper: Remove words
av8tordude
09-03-2011, 05:34 PM
in a cell I have "8.0 (Evaluation Mode)". How can i remove only "(Evaluation Mode)?
mikerickson
09-03-2011, 11:07 PM
What is objectionable about "(Evaluation Mode)"?
The fact that it is inside parenthesis, or that it is after the first left parenthesis?
Perhaps
=SUBSTITUTE(A1, "("&TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"(",")"),")",REPT(" ",255)),255,255))&")", "")
or
=TRIM(LEFT(SUBSTITUTE(A1,"(",REPT(" ",255)), 255)) might work
av8tordude
09-04-2011, 06:15 AM
Mike,
Can this done with VBA?
The "(Evaluation Mode)" will be removed when the user enters a password.
mikerickson
09-04-2011, 02:09 PM
Yes it could.
What result do you want from:
"John Smith (marry mode) Dave(single friend) Cat"
What code have you tried?
av8tordude
09-04-2011, 02:23 PM
Mike,
the result i'm trying to achieve is to delete (Evaluation Mode) from 8.1 (Evaluation Mode) resulting in 8.1 remaining. The purpose for this is for display only.
mikerickson
09-04-2011, 02:44 PM
Would =SUBSTITUTE(A1, "(Evaluation Mode)", "") do the job?
av8tordude
09-04-2011, 02:48 PM
Yes it probably would, but that looks like a formula rather then vba? I would need to a vba to execute the change when the user enters a password.
mikerickson
09-04-2011, 03:20 PM
If its that straight forward, you could record a macro of you Find&Replacing "(Evaluation Mode)" with "" and use that.
Paul_Hossler
09-04-2011, 08:04 PM
If you just want to replace a cell substring using VBA then
Option Explicit
Sub NoMoreEval()
ActiveSheet.Cells(1, 1).Value = "Ver 8.1 (Evaluation Mode)"
MsgBox ActiveSheet.Cells(1, 1).Value
Call ActiveSheet.Cells(1, 1).Replace(" (Evaluation Mode)", vbNullString, xlPart)
MsgBox ActiveSheet.Cells(1, 1).Value
End Sub
Paul
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.