Dear All,

I am looking for a formula that will change all the cells value if that
contains the specified word. e.g.

GPS Ignition off
Unlock Ignition off
Health check Ignition off
GPS Unlock Ignition off

if a cell contains Ignition off then cell value should change with Ignition
off and remove the other status with Ignition off.

Thanks

You can't change a cell value by a formula. Yu can use a helper column with a
formula in it returning the required result:
=TRIM(SUBSTITUTE(A1,"Ignition off",""))

Regards,
Stefi


„Hassanâ€� ezt Ã*rta:

> Dear All,
>
> I am looking for a formula that will change all the cells value if that
> contains the specified word. e.g.
>
> GPS Ignition off
> Unlock Ignition off
> Health check Ignition off
> GPS Unlock Ignition off
>
> if a cell contains Ignition off then cell value should change with Ignition
> off and remove the other status with Ignition off.
>
> Thanks

Dear Stefi,

Thanks for reply, following formula only remove Ignition off and other word
is still there, I want remove other word with Ignition off

Thanks

"Stefi" wrote:

> You can't change a cell value by a formula. Yu can use a helper column with a
> formula in it returning the required result:
> =TRIM(SUBSTITUTE(A1,"Ignition off",""))
>
> Regards,
> Stefi
>
>
> „Hassanâ€� ezt Ã*rta:
>
> > Dear All,
> >
> > I am looking for a formula that will change all the cells value if that
> > contains the specified word. e.g.
> >
> > GPS Ignition off
> > Unlock Ignition off
> > Health check Ignition off
> > GPS Unlock Ignition off
> >
> > if a cell contains Ignition off then cell value should change with Ignition
> > off and remove the other status with Ignition off.
> >
> > Thanks

Do you mean that cells containing Ignition off should change to mere Ignition
off and all other words should be removed?
E.g.
Old content new content
GPS Ignition off Ignition off
Unlock Ignition off Ignition off
Health check Ignition off Ignition off
GPS Unlock Ignition off Ignition off

Stefi


„Hassanâ€� ezt Ã*rta:

> Dear Stefi,
>
> Thanks for reply, following formula only remove Ignition off and other word
> is still there, I want remove other word with Ignition off
>
> Thanks
>
> "Stefi" wrote:
>
> > You can't change a cell value by a formula. Yu can use a helper column with a
> > formula in it returning the required result:
> > =TRIM(SUBSTITUTE(A1,"Ignition off",""))
> >
> > Regards,
> > Stefi
> >
> >
> > „Hassanâ€� ezt Ã*rta:
> >
> > > Dear All,
> > >
> > > I am looking for a formula that will change all the cells value if that
> > > contains the specified word. e.g.
> > >
> > > GPS Ignition off
> > > Unlock Ignition off
> > > Health check Ignition off
> > > GPS Unlock Ignition off
> > >
> > > if a cell contains Ignition off then cell value should change with Ignition
> > > off and remove the other status with Ignition off.
> > >
> > > Thanks

Yes Stefi, Exactly same.

"Stefi" wrote:

> Do you mean that cells containing Ignition off should change to mere Ignition
> off and all other words should be removed?
> E.g.
> Old content new content
> GPS Ignition off Ignition off
> Unlock Ignition off Ignition off
> Health check Ignition off Ignition off
> GPS Unlock Ignition off Ignition off
>
> Stefi
>
>
> „Hassanâ€� ezt Ã*rta:
>
> > Dear Stefi,
> >
> > Thanks for reply, following formula only remove Ignition off and other word
> > is still there, I want remove other word with Ignition off
> >
> > Thanks
> >
> > "Stefi" wrote:
> >
> > > You can't change a cell value by a formula. Yu can use a helper column with a
> > > formula in it returning the required result:
> > > =TRIM(SUBSTITUTE(A1,"Ignition off",""))
> > >
> > > Regards,
> > > Stefi
> > >
> > >
> > > „Hassanâ€� ezt Ã*rta:
> > >
> > > > Dear All,
> > > >
> > > > I am looking for a formula that will change all the cells value if that
> > > > contains the specified word. e.g.
> > > >
> > > > GPS Ignition off
> > > > Unlock Ignition off
> > > > Health check Ignition off
> > > > GPS Unlock Ignition off
> > > >
> > > > if a cell contains Ignition off then cell value should change with Ignition
> > > > off and remove the other status with Ignition off.
> > > >
> > > > Thanks

Does this work for you:

=IF(COUNTIF(A1,"*ignition off")>0,"Ignition off",A1)
?
Copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Hassan" <(E-Mail Removed)> wrote in message
news2222EF1-7169-4F4D-8F4D-(E-Mail Removed)...
> Yes Stefi, Exactly same.
>
> "Stefi" wrote:
>
> > Do you mean that cells containing Ignition off should change to mere
Ignition
> > off and all other words should be removed?
> > E.g.
> > Old content new content
> > GPS Ignition off Ignition off
> > Unlock Ignition off Ignition off
> > Health check Ignition off Ignition off
> > GPS Unlock Ignition off Ignition off
> >
> > Stefi
> >
> >
> > „Hassanâ€� ezt Ã*rta:
> >
> > > Dear Stefi,
> > >
> > > Thanks for reply, following formula only remove Ignition off and other
word
> > > is still there, I want remove other word with Ignition off
> > >
> > > Thanks
> > >
> > > "Stefi" wrote:
> > >
> > > > You can't change a cell value by a formula. Yu can use a helper
column with a
> > > > formula in it returning the required result:
> > > > =TRIM(SUBSTITUTE(A1,"Ignition off",""))
> > > >
> > > > Regards,
> > > > Stefi
> > > >
> > > >
> > > > „Hassanâ€� ezt Ã*rta:
> > > >
> > > > > Dear All,
> > > > >
> > > > > I am looking for a formula that will change all the cells value if
that
> > > > > contains the specified word. e.g.
> > > > >
> > > > > GPS Ignition off
> > > > > Unlock Ignition off
> > > > > Health check Ignition off
> > > > > GPS Unlock Ignition off
> > > > >
> > > > > if a cell contains Ignition off then cell value should change with
Ignition
> > > > > off and remove the other status with Ignition off.
> > > > >
> > > > > Thanks

Thanks for reply Ragdyer, but following is not working.

"Ragdyer" wrote:

> Does this work for you:
>
> =IF(COUNTIF(A1,"*ignition off")>0,"Ignition off",A1)
> ?
> Copy down as needed.
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Hassan" <(E-Mail Removed)> wrote in message
> news2222EF1-7169-4F4D-8F4D-(E-Mail Removed)...
> > Yes Stefi, Exactly same.
> >
> > "Stefi" wrote:
> >
> > > Do you mean that cells containing Ignition off should change to mere
> Ignition
> > > off and all other words should be removed?
> > > E.g.
> > > Old content new content
> > > GPS Ignition off Ignition off
> > > Unlock Ignition off Ignition off
> > > Health check Ignition off Ignition off
> > > GPS Unlock Ignition off Ignition off
> > >
> > > Stefi
> > >
> > >
> > > „Hassanâ€� ezt Ã*rta:
> > >
> > > > Dear Stefi,
> > > >
> > > > Thanks for reply, following formula only remove Ignition off and other
> word
> > > > is still there, I want remove other word with Ignition off
> > > >
> > > > Thanks
> > > >
> > > > "Stefi" wrote:
> > > >
> > > > > You can't change a cell value by a formula. Yu can use a helper
> column with a
> > > > > formula in it returning the required result:
> > > > > =TRIM(SUBSTITUTE(A1,"Ignition off",""))
> > > > >
> > > > > Regards,
> > > > > Stefi
> > > > >
> > > > >
> > > > > „Hassanâ€� ezt Ã*rta:
> > > > >
> > > > > > Dear All,
> > > > > >
> > > > > > I am looking for a formula that will change all the cells value if
> that
> > > > > > contains the specified word. e.g.
> > > > > >
> > > > > > GPS Ignition off
> > > > > > Unlock Ignition off
> > > > > > Health check Ignition off
> > > > > > GPS Unlock Ignition off
> > > > > >
> > > > > > if a cell contains Ignition off then cell value should change with
> Ignition
> > > > > > off and remove the other status with Ignition off.
> > > > > >
> > > > > > Thanks
>
>

Hassan

I note some inconsistency with your requirements.

Your example shows you want result to be ignition off

You then told Stefi you want just ignition

Whatever the case, RD's formula will work on your example data but you may
want to alter it slightly for the second case.

=IF(COUNTIF(A1,"*ignition off")>0,"ignition",A1)


Gord Dibben MS Excel MVP


On Sun, 27 Jul 2008 01:08:01 -0700, Hassan
<(E-Mail Removed)> wrote:

>Thanks for reply Ragdyer, but following is not working.
>
>"Ragdyer" wrote:
>
>> Does this work for you:
>>
>> =IF(COUNTIF(A1,"*ignition off")>0,"Ignition off",A1)
>> ?
>> Copy down as needed.
>> --
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Hassan" <(E-Mail Removed)> wrote in message
>> news2222EF1-7169-4F4D-8F4D-(E-Mail Removed)...
>> > Yes Stefi, Exactly same.
>> >
>> > "Stefi" wrote:
>> >
>> > > Do you mean that cells containing Ignition off should change to mere
>> Ignition
>> > > off and all other words should be removed?
>> > > E.g.
>> > > Old content new content
>> > > GPS Ignition off Ignition off
>> > > Unlock Ignition off Ignition off
>> > > Health check Ignition off Ignition off
>> > > GPS Unlock Ignition off Ignition off
>> > >
>> > > Stefi
>> > >
>> > >
>> > > „Hassan” ezt írta:
>> > >
>> > > > Dear Stefi,
>> > > >
>> > > > Thanks for reply, following formula only remove Ignition off and other
>> word
>> > > > is still there, I want remove other word with Ignition off
>> > > >
>> > > > Thanks
>> > > >
>> > > > "Stefi" wrote:
>> > > >
>> > > > > You can't change a cell value by a formula. Yu can use a helper
>> column with a
>> > > > > formula in it returning the required result:
>> > > > > =TRIM(SUBSTITUTE(A1,"Ignition off",""))
>> > > > >
>> > > > > Regards,
>> > > > > Stefi
>> > > > >
>> > > > >
>> > > > > „Hassan” ezt írta:
>> > > > >
>> > > > > > Dear All,
>> > > > > >
>> > > > > > I am looking for a formula that will change all the cells value if
>> that
>> > > > > > contains the specified word. e.g.
>> > > > > >
>> > > > > > GPS Ignition off
>> > > > > > Unlock Ignition off
>> > > > > > Health check Ignition off
>> > > > > > GPS Unlock Ignition off
>> > > > > >
>> > > > > > if a cell contains Ignition off then cell value should change with
>> Ignition
>> > > > > > off and remove the other status with Ignition off.
>> > > > > >
>> > > > > > Thanks
>>
>>

To save everyone the necessity of continued *guessing* at exactly what
you're looking for, would you at least extend us all the courtesy of
informing us as to what didn't work?

What did you get that you didn't want,
OR
what didn't you get that you wanted!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Hassan" <(E-Mail Removed)> wrote in message
news:3FE21514-857A-411F-84D4-(E-Mail Removed)...
> Thanks for reply Ragdyer, but following is not working.
>
> "Ragdyer" wrote:
>
> > Does this work for you:
> >
> > =IF(COUNTIF(A1,"*ignition off")>0,"Ignition off",A1)
> > ?
> > Copy down as needed.
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Hassan" <(E-Mail Removed)> wrote in message
> > news2222EF1-7169-4F4D-8F4D-(E-Mail Removed)...
> > > Yes Stefi, Exactly same.
> > >
> > > "Stefi" wrote:
> > >
> > > > Do you mean that cells containing Ignition off should change to mere
> > Ignition
> > > > off and all other words should be removed?
> > > > E.g.
> > > > Old content new content
> > > > GPS Ignition off Ignition off
> > > > Unlock Ignition off Ignition off
> > > > Health check Ignition off Ignition off
> > > > GPS Unlock Ignition off Ignition off
> > > >
> > > > Stefi
> > > >
> > > >
> > > > „Hassanâ€� ezt Ã*rta:
> > > >
> > > > > Dear Stefi,
> > > > >
> > > > > Thanks for reply, following formula only remove Ignition off and
other
> > word
> > > > > is still there, I want remove other word with Ignition off
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Stefi" wrote:
> > > > >
> > > > > > You can't change a cell value by a formula. Yu can use a helper
> > column with a
> > > > > > formula in it returning the required result:
> > > > > > =TRIM(SUBSTITUTE(A1,"Ignition off",""))
> > > > > >
> > > > > > Regards,
> > > > > > Stefi
> > > > > >
> > > > > >
> > > > > > „Hassanâ€� ezt Ã*rta:
> > > > > >
> > > > > > > Dear All,
> > > > > > >
> > > > > > > I am looking for a formula that will change all the cells
value if
> > that
> > > > > > > contains the specified word. e.g.
> > > > > > >
> > > > > > > GPS Ignition off
> > > > > > > Unlock Ignition off
> > > > > > > Health check Ignition off
> > > > > > > GPS Unlock Ignition off
> > > > > > >
> > > > > > > if a cell contains Ignition off then cell value should change
with
> > Ignition
> > > > > > > off and remove the other status with Ignition off.
> > > > > > >
> > > > > > > Thanks
> >
> >

Thanks Ragdyer,

Can I use two statuses instead of one, e.g. Ignition off and Startup as well.

Thanks

Hassan

"Ragdyer" wrote:

> To save everyone the necessity of continued *guessing* at exactly what
> you're looking for, would you at least extend us all the courtesy of
> informing us as to what didn't work?
>
> What did you get that you didn't want,
> OR
> what didn't you get that you wanted!
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Hassan" <(E-Mail Removed)> wrote in message
> news:3FE21514-857A-411F-84D4-(E-Mail Removed)...
> > Thanks for reply Ragdyer, but following is not working.
> >
> > "Ragdyer" wrote:
> >
> > > Does this work for you:
> > >
> > > =IF(COUNTIF(A1,"*ignition off")>0,"Ignition off",A1)
> > > ?
> > > Copy down as needed.
> > > --
> > > HTH,
> > >
> > > RD
> > >
> >
> > --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may benefit
> !
> >
> > --------------------------------------------------------------------------
> -
> > > "Hassan" <(E-Mail Removed)> wrote in message
> > > news2222EF1-7169-4F4D-8F4D-(E-Mail Removed)...
> > > > Yes Stefi, Exactly same.
> > > >
> > > > "Stefi" wrote:
> > > >
> > > > > Do you mean that cells containing Ignition off should change to mere
> > > Ignition
> > > > > off and all other words should be removed?
> > > > > E.g.
> > > > > Old content new content
> > > > > GPS Ignition off Ignition off
> > > > > Unlock Ignition off Ignition off
> > > > > Health check Ignition off Ignition off
> > > > > GPS Unlock Ignition off Ignition off
> > > > >
> > > > > Stefi
> > > > >
> > > > >
> > > > > „Hassanâ€� ezt Ã*rta:
> > > > >
> > > > > > Dear Stefi,
> > > > > >
> > > > > > Thanks for reply, following formula only remove Ignition off and
> other
> > > word
> > > > > > is still there, I want remove other word with Ignition off
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > "Stefi" wrote:
> > > > > >
> > > > > > > You can't change a cell value by a formula. Yu can use a helper
> > > column with a
> > > > > > > formula in it returning the required result:
> > > > > > > =TRIM(SUBSTITUTE(A1,"Ignition off",""))
> > > > > > >
> > > > > > > Regards,
> > > > > > > Stefi
> > > > > > >
> > > > > > >
> > > > > > > „Hassanâ€� ezt Ã*rta:
> > > > > > >
> > > > > > > > Dear All,
> > > > > > > >
> > > > > > > > I am looking for a formula that will change all the cells
> value if
> > > that
> > > > > > > > contains the specified word. e.g.
> > > > > > > >
> > > > > > > > GPS Ignition off
> > > > > > > > Unlock Ignition off
> > > > > > > > Health check Ignition off
> > > > > > > > GPS Unlock Ignition off
> > > > > > > >
> > > > > > > > if a cell contains Ignition off then cell value should change
> with
> > > Ignition
> > > > > > > > off and remove the other status with Ignition off.
> > > > > > > >
> > > > > > > > Thanks
> > >
> > >
>
>