+ Reply to Thread
Results 1 to 5 of 5

Split text in a column using an entire word and not just a single character

  1. #1
    Registered User
    Join Date
    09-20-2019
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    9

    Split text in a column using an entire word and not just a single character

    I have an excel doc with multiple addresses, and some of them are in the form:

    312 Main St. Suite 6
    1234 Victoria St PO Box 231
    12 Amherst Rd Unit 3

    What I want to do is if the addresses contain either Suite, PO Box, or Unit, I want to remove it from the current column and put it into the column next to it. How do I go about doing this in Excel?

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: Split text in a column using an entire word and not just a single character

    and what about the number that follows those?

    But based on your description I'm not sure it can be done without VBA.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    Re: Split text in a column using an entire word and not just a single character

    Sambo is correct, you would need to use VBA, otherwise it would require two columns of formulas - one to extract the left half of the address, and the other to get the right (Suite/Unit/etc.).

    Also note that any code or formula may need to handle exceptions, e.g. "PO Box" vs. "P.O. Box" or "Suite #" vs. "Ste. #". (Unless your data is perfect, of course.)

  4. #4
    Registered User
    Join Date
    09-20-2019
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    9

    Re: Split text in a column using an entire word and not just a single character

    Yes, what I want to do is get the numbers that come after it as well. I have to automate uploading contact info from an excel doc into a website, but if the addresses surpass 30 characters, I cannot input them into only 1 line. So in that case I want to split it into 2 lines and figured it would be best to do split it from Suite #, PO Box, and Unit #.

    I do not have much experience with vba... Do you have any advice on how I should begin?

    Thank you for your replies :D

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,369

    Re: Split text in a column using an entire word and not just a single character

    A moderator can move this to the VBA section of the forum. I don't have VBA skills but many on this forum do.

+ 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. Split text in a column using an entire word and not just a single character
    By hisuh1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2019, 02:31 PM
  2. [SOLVED] Delete Entire Row where there is text (word) in the Column
    By Patish in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2016, 09:55 AM
  3. [SOLVED] Split text by character
    By benbi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-25-2015, 02:38 PM
  4. Split a single word document into multiple word documents
    By CaptainCool in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2014, 03:31 AM
  5. Having problem with text to column split on a particular word
    By alicia ch in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2012, 12:29 PM
  6. Replies: 10
    Last Post: 10-24-2012, 04:02 AM
  7. vlookup,replace word with single character
    By vascott01 in forum Excel General
    Replies: 6
    Last Post: 03-05-2010, 09:04 AM

Tags for this Thread

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