+ Reply to Thread
Results 1 to 6 of 6

How to erase some words on a cell?

  1. #1
    Registered User
    Join Date
    08-12-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    8

    How to erase some words on a cell?

    This is actually a simple formula, but i just forgot it because it's a long time ago that i used it.
    so i have a Sheet with like 500 rows contain

    JVHSAKJHAB12-11 with date 12/08/2011
    IKSH12 with date 11/06/2011
    :
    :
    etc...

    so, i remember i can erase the word after "with date" and before "with date" with "*"
    i just forget where to put it and what's the formula?
    Can you help me guys?
    i know i can do it by replacing the "with date" with any symbol then separate them with Text to Column, but i just want to remember exactly how to do it by "*"

    Thanks in advance guys.

  2. #2
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: How to erase some words on a cell?

    Please try this function if it works fine.

    =TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"with date",REPT("!",LEN(A1))),LEN(A1)),"!",""))
    Please click 'Add reputation', if my answer helped you.

  3. #3
    Forum Contributor
    Join Date
    06-26-2013
    Location
    LAX
    MS-Off Ver
    Excel 2007,Excel 2010
    Posts
    363

    Re: How to erase some words on a cell?

    If you would like to remove the "with date".
    Just find and replace, find - "with date" replace with "empty"

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: How to erase some words on a cell?

    In A1 Cell
    JVHSAKJHAB12-11 with date 12/08/2011


    In B1 Cell
    =RIGHT(TRIM(A1),10)

    Drag it down...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: How to erase some words on a cell?

    =MID(A1,FIND("with date",A1)+LEN("with date"),LEN(A1))

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: How to erase some words on a cell?

    erase the word after "with date"
    @elayaz
    Just find and replace, find - "with date" replace with "empty"
    You forgot the "*"
    Just find and replace, find - "with date*" replace with "empty"
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Erase cell that are unprotect AND not in the first row.
    By Melisendk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2013, 02:34 AM
  2. Find phrase and erase all other words
    By csch123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2012, 02:07 PM
  3. Erase contents in cell based on Strike through.
    By Slomaro2000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2008, 02:54 PM
  4. macro that would erase part of data in a cell?
    By tradewindrider in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-17-2007, 02:09 PM
  5. [SOLVED] how to erase all spaces in text cell?
    By D in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2006, 03:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1