+ Reply to Thread
Results 1 to 11 of 11

Split Text to columns using an entire word, not just a single cha

  1. #1
    tomhomestroops
    Guest

    Split Text to columns using an entire word, not just a single cha

    I have a file with info in a cell that looks like:

    123 South Main St. Suite 6

    I want to do text-to-columns, splitting each cell out based on the word
    Suite. I can’t do it by a space delimiter because it will break it out in
    too many pieces. Can I use the "other" delimiter choice but put in an entire
    word rather than just one character?

    Any suggestions?

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    Use a 2 step process.

    First create a formula in J9 =FIND("suite",K9)

    Then in H9 put =LEFT(K9,J9-1)

    You can trim the right of the text using Right()

    Matt

  3. #3
    Elkar
    Guest

    RE: Split Text to columns using an entire word, not just a single cha

    You can only use a single character as a delimiter. But here's something you
    could try:

    Do a Find/Replace over your range of data.
    Find: suite
    Replace with: ~suite
    Now, use Text-to-Columns, and use the ~ character as your delimiter.

    Note, if you happen to have ~ in your data already, pick a different symbol
    that doesn't appear.

    HTH,
    Elkar


    "tomhomestroops" wrote:

    > I have a file with info in a cell that looks like:
    >
    > 123 South Main St. Suite 6
    >
    > I want to do text-to-columns, splitting each cell out based on the word
    > Suite. I can’t do it by a space delimiter because it will break it out in
    > too many pieces. Can I use the "other" delimiter choice but put in an entire
    > word rather than just one character?
    >
    > Any suggestions?


  4. #4
    bpeltzer
    Guest

    RE: Split Text to columns using an entire word, not just a single cha

    You could use a couple of formulas with the FIND function to create the
    'before' and 'after' portions. If, for instance, your address is in A1, then
    =TRIM(LEFT(A1,FIND("Suite",A1)-1)) will get the 'before' part and
    =TRIM(RIGHT(A1,LEN(A1)+1-FIND("Suite",A1))) will get the 'after'.
    If you put those formulas in B1 and C1, and copy those cells down into as
    many rows as you need, you'll wind up with columns B and C showing the split
    from column A. Then you could copy / paste values in columns B & C, then
    delete column A.
    (Note that the formulas shown don't test for the absence of the word Suite
    in the address!).

    "tomhomestroops" wrote:

    > I have a file with info in a cell that looks like:
    >
    > 123 South Main St. Suite 6
    >
    > I want to do text-to-columns, splitting each cell out based on the word
    > Suite. I can’t do it by a space delimiter because it will break it out in
    > too many pieces. Can I use the "other" delimiter choice but put in an entire
    > word rather than just one character?
    >
    > Any suggestions?


  5. #5
    tomhomestroops
    Guest

    RE: Split Text to columns using an entire word, not just a single

    VERY clever! All 3 solutions just posted would work, but this one seems to
    be the best for my situation. I'll try it out and let you know if it works,
    but it sounds like it should.

    "Elkar" wrote:

    > You can only use a single character as a delimiter. But here's something you
    > could try:
    >
    > Do a Find/Replace over your range of data.
    > Find: suite
    > Replace with: ~suite
    > Now, use Text-to-Columns, and use the ~ character as your delimiter.
    >
    > Note, if you happen to have ~ in your data already, pick a different symbol
    > that doesn't appear.
    >
    > HTH,
    > Elkar
    >
    >
    > "tomhomestroops" wrote:
    >
    > > I have a file with info in a cell that looks like:
    > >
    > > 123 South Main St. Suite 6
    > >
    > > I want to do text-to-columns, splitting each cell out based on the word
    > > Suite. I can’t do it by a space delimiter because it will break it out in
    > > too many pieces. Can I use the "other" delimiter choice but put in an entire
    > > word rather than just one character?
    > >
    > > Any suggestions?


  6. #6
    tomhomestroops
    Guest

    RE: Split Text to columns using an entire word, not just a single

    SHWEEEET! Or shall I say Suite!

    That worked. I had to do a second search/reaplce once I parsed it to remove
    an extra space in the 2nd block, but it worked fine.

    Excellent solution.

    "Elkar" wrote:

    > You can only use a single character as a delimiter. But here's something you
    > could try:
    >
    > Do a Find/Replace over your range of data.
    > Find: suite
    > Replace with: ~suite
    > Now, use Text-to-Columns, and use the ~ character as your delimiter.
    >
    > Note, if you happen to have ~ in your data already, pick a different symbol
    > that doesn't appear.
    >
    > HTH,
    > Elkar
    >
    >
    > "tomhomestroops" wrote:
    >
    > > I have a file with info in a cell that looks like:
    > >
    > > 123 South Main St. Suite 6
    > >
    > > I want to do text-to-columns, splitting each cell out based on the word
    > > Suite. I can’t do it by a space delimiter because it will break it out in
    > > too many pieces. Can I use the "other" delimiter choice but put in an entire
    > > word rather than just one character?
    > >
    > > Any suggestions?


  7. #7
    tomhomestroops
    Guest

    RE: Split Text to columns using an entire word, not just a single

    VERY Clever. Downright elegant.

    That worked fine. Thanks much.



    "Elkar" wrote:

    > You can only use a single character as a delimiter. But here's something you
    > could try:
    >
    > Do a Find/Replace over your range of data.
    > Find: suite
    > Replace with: ~suite
    > Now, use Text-to-Columns, and use the ~ character as your delimiter.
    >
    > Note, if you happen to have ~ in your data already, pick a different symbol
    > that doesn't appear.
    >
    > HTH,
    > Elkar
    >
    >
    > "tomhomestroops" wrote:
    >
    > > I have a file with info in a cell that looks like:
    > >
    > > 123 South Main St. Suite 6
    > >
    > > I want to do text-to-columns, splitting each cell out based on the word
    > > Suite. I can’t do it by a space delimiter because it will break it out in
    > > too many pieces. Can I use the "other" delimiter choice but put in an entire
    > > word rather than just one character?
    > >
    > > Any suggestions?


  8. #8
    tomhomestroops
    Guest

    RE: Split Text to columns using an entire word, not just a single

    Sorry about the multiple posts. This is the first time I used this forum and
    I thought my prior posts had disappeared into the ether.

    "Elkar" wrote:

    > You can only use a single character as a delimiter. But here's something you
    > could try:
    >
    > Do a Find/Replace over your range of data.
    > Find: suite
    > Replace with: ~suite
    > Now, use Text-to-Columns, and use the ~ character as your delimiter.
    >
    > Note, if you happen to have ~ in your data already, pick a different symbol
    > that doesn't appear.
    >
    > HTH,
    > Elkar
    >
    >
    > "tomhomestroops" wrote:
    >
    > > I have a file with info in a cell that looks like:
    > >
    > > 123 South Main St. Suite 6
    > >
    > > I want to do text-to-columns, splitting each cell out based on the word
    > > Suite. I can’t do it by a space delimiter because it will break it out in
    > > too many pieces. Can I use the "other" delimiter choice but put in an entire
    > > word rather than just one character?
    > >
    > > Any suggestions?


  9. #9
    Registered User
    Join Date
    06-04-2006
    Posts
    7
    bpeltzer makes a valid point... let me expand on it a bit.

    Text to columns along with Search & Replace are fantastic and work great in for many situations, but there are also times when data you'd like to manipulate doesn't necessarily fit into a generic model, or have an ongoing need that would be best resolved by using an Excel Function. Well, not to worry, Excel has many tricks up it's sleeve to help you in almost every situation.

    The tricks I speak of with regard to your question are what's known as Text Functions. See below for a short list and general format.

    LEFT - LEFT(text; num_chars)
    MID - MID(text;start_num;num_chars)
    RIGHT - RIGHT(text; num_chars)
    SEARCH - SEARCH(find_text;within_text;start_num)
    LEN - LEN(text)

    Alrighty then, using some of Excel's powerful Text functions... lets go get us some text shall we? Buckle up!

    Examples:

    Using your text "123 South Main St. Suite 6" and

    Text is assumed to be in cell A1
    Formula can be placed in any cell except A1

    The following formula returns the first word or block of characters from a string, which in this case is "123"

    =LEFT(A1,FIND(" ",A1)-1)

    The following formula returns all the characters in a cell preceding the occurrence of "St", which in this case is "123 South Main St"

    =LEFT(A1,FIND("St",A1)+1)

    The following formula returns the last word or block of characters in a string, which in this case is "6"

    =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

    The following formula searches for " Suite", trims the leading empty space character, returning "Suite" and all the words or block of characters that follow, which in this case is "Suite 6"

    =RIGHT(A1,LEN(A1)-FIND(" Suite",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

    The following formula returns all but the first word or block of characters in a string, which in this case is "South Main St. Suite 6"

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

    Hope you found this useful!

    Danny Tedesco
    http://www.wirelessphonecity.com
    Cut the cables and let your data fly...

  10. #10
    Registered User
    Join Date
    10-23-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Split Text to columns using an entire word, not just a single cha

    I have a long list of contacts in which I need to extract contact's first name, last name, and email address and place them each in their own columns. In some cases, there is no first/last name, in that case first name = temp and last name = company name. Is there a way of singling out this information and have it placed automatically in their own columns?

    eg.
    "Joe helpfull
    Executive board/managing director (Business, corporate or plant management)
    Company name
    1234 Smallroad, LA
    USA

    Phone:
    +1 243 234 2343
    Fax:
    +1 234 323 2342
    Email:
    joe@emailaddress.com
    Internet Address:
    http://www.emailaddress.com"

    Thanks!

  11. #11
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: Split Text to columns using an entire word, not just a single cha

    templatemayhem,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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