+ Reply to Thread
Results 1 to 6 of 6

Remove words to the left of a colon in multiple cells

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2011
    Posts
    2

    Remove words to the left of a colon in multiple cells

    Hello,

    I am trying to remove words/letter and such to the left of a colon in multiple cells. Each cell has different things in it and i need to shorten them. below is an example of what i'm referring to. NOw not only do i need to know the formula but i need to know how to do it, step by step. I know tedious, but i need this.

    eWFusePCNEW : eWFusePCNEW-NB-3000
    eWFusePCNEW : eWFusePCNEW-NB-3500
    eWFusePCNEW : eWFusePCNEW-NB-3500
    eWFusePCNEW : eWFusePCNEW-NB-4000


    Any help would be great.

    Thanks.

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Remove words to the left of a colon in multiple cells

    Hello,

    One way to do it is pasting this formula in B1 and drag it down
    Please Login or Register  to view this content.
    Assuming you have all of those name in Column A, starting from A1.
    Here is a sample file.
    Attached Files Attached Files
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Remove words to the left of a colon in multiple cells

    Try
    =REPLACE(A1,1,FIND(":",A1),"")

    Find will find where the colon Is and return it's position #
    =FIND(":",A1)
    In this case, that is 13

    Then Replace can 'replace' text from the given start# of the string
    and use the position# to determine how long of a string to replace
    And replace it with Nothing ""
    =REPLACE(A1,1,13,"") <-replaces 13 characters beginning with 1, replaces that with nothing ""

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Remove words to the left of a colon in multiple cells

    And if you don't want that leading blank before your results, just modify Jonmo1's formula very slightly to: =REPLACE(A1,1,FIND(":",A1)+1,"")

  5. #5
    Registered User
    Join Date
    04-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2011
    Posts
    2

    Re: Remove words to the left of a colon in multiple cells

    Thank you all so very much. It worked.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2504
    Posts
    13,640

    Re: Remove words to the left of a colon in multiple cells

    Formulas are nice,but... - Much easier : Data Text to columns choose : as delimiter - choose to not import the first column - Done

+ 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