+ Reply to Thread
Results 1 to 6 of 6

Delete certain text from a range of cells

  1. #1
    Registered User
    Join Date
    11-19-2012
    Location
    Colorado
    MS-Off Ver
    Excel 2003
    Posts
    12

    Delete certain text from a range of cells

    I have exported data from quickbooks and one of the fields contains our clients' names, but it is preceeded by other information I'd like to delete. So the fields look something like "Teaching Tree:lastname, firstname"

    How can I select a range of cells and have it delete only the "Treaching Tree:" from the fields and leave the clients' names?

    Thanks!

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

    Re: Delete certain text from a range of cells

    Assume that the data is present from A1 cell

    A1 cell
    Teaching Tree:lastname, firstname

    In B1 cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag it down

    Change the cell reference A1 to your desired cell, if required.


    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

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Delete certain text from a range of cells

    You could use something like this:

    =RIGHT(A1,LEN(A1)-FIND(":",A1))

    This assumes that there is always a : immediately followed by the names you want returned.

    - Moo

  4. #4
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Delete certain text from a range of cells

    Suppose that "Teaching Tree:lastname, firstname" is in A1 cell. Then formula would be like that:

    =SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-FIND(":",A1,1)),"",1)

    Result: Teaching Tree:

  5. #5
    Registered User
    Join Date
    11-19-2012
    Location
    Colorado
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Delete certain text from a range of cells

    Holy cow there are a lot of ways to accomplish this! Thanks!

  6. #6
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Delete certain text from a range of cells

    Another way is use Text to columns

    Select the text > Data Tab > Data Tools > Text to Columns > Delimited > Tick Other box & type the colon in the box > Next > Ensure the first column is selected and tick box Do not Import column > Finish

    Took longer to type then carry out!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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