+ Reply to Thread
Results 1 to 5 of 5

Split words to another column from chosen word

  1. #1
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Split words to another column from chosen word

    Giday to all

    There are several rows in the spreadsheet with addresses in Column C. I want to split the rest of the words that start with "from" and split these words to another column. Or if possible, to delete the words that start with "from". Because the spreadsheet has already have the street names in other columns (Columns D & E)
    I've enclosed the file for a better understanding.

    Thank you
    Attached Files Attached Files
    Last edited by unley; 09-05-2016 at 10:00 PM.
    I'm using MS Office 2013

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,987

    Re: Split words to another column from chosen word

    This should do it

    =SUBSTITUTE(LEFT(C3,FIND("from",C3)-2),"Surface - ","")
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Re: Split words to another column from chosen word

    Fantastic, thanks so much for the useful formula Crooza. I wish I can see many other similar functions available for splitting and deleting the columns

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Split words to another column from chosen word

    Enter formula in D3 and pull it to the right to E3 and then down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AlKey; 09-05-2016 at 10:17 PM. Reason: Added Excel spreadsheet
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Re: Split words to another column from chosen word

    Thanks Alkey.
    This did not work with Column D (that when I created another column to make it D) but it did work in Column M !!
    I wonder why.
    This same with Crooza's formula too.

+ 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] Search a column for up to 30 words then insert a different word in a separate column
    By SFore in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-18-2014, 05:13 PM
  2. Replies: 3
    Last Post: 07-15-2014, 03:42 PM
  3. [SOLVED] Split words in column and add results
    By adambot in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-07-2014, 08:55 AM
  4. [SOLVED] Split a line of words, each word in a separate cell
    By Hitch75 in forum Excel General
    Replies: 13
    Last Post: 08-08-2012, 10:10 PM
  5. [SOLVED] Split a line of words, each word in a separate cell using function
    By slc in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-08-2012, 10:03 PM
  6. Split cell so that the last word in separate column
    By CameronP in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-03-2012, 02:01 AM
  7. Replies: 2
    Last Post: 04-29-2011, 10:53 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