PDA

View Full Version : 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