+ Reply to Thread
Results 1 to 18 of 18

splitting text (address)

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    splitting text (address)

    Hey all i am new to excel and i am trying to find a quicker way to sort my data.
    I have a list of addresses in column A and i would like the data to be split and displayed like in the picture (dataescel.png) without actually manually splitting 10000 rows of data. any help would be greatly appreciated thank you
    Attached Files Attached Files
    Last edited by Edwardanson; 12-12-2012 at 10:59 PM.

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: splitting text (address)

    Welcome to the forum Edward,

    Want to get your question answered quickly?

    Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    12-12-2012
    Location
    australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: splitting text (address)

    okay Thank You for the advice and quick reply i have edited my post to now include the attachment

  4. #4
    Forum Contributor
    Join Date
    08-30-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: splitting text (address)

    I think u need vba. i already attach a simple workbook.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: splitting text (address)

    im not even sure VBA will help you with this. based on the sample provided (and logic?), the 1st split - company - could be a 1- or 2-word phrase, the address could be a number (or not) and again a 1- or 2-word phrase, the town and state could both also be either 1- or 2-words. there is just no way (that i can see) of splittiing this the way that would be meaningful

    company adress town state postcode
    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
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    08-30-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: splitting text (address)

    i gave him an option by using a userform to fill up the company name, address, town and postcode. later, all the words will be combine in one cell. its true to split the words are hard to do, but it would be nice to give an option.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: splitting text (address)

    imram, the userform is a great option - for future entries it wont help with the existing data though

  8. #8
    Registered User
    Join Date
    12-12-2012
    Location
    australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: splitting text (address)

    Thanks heaps for all your help so far. i was wondering if it would be possible to split the text based on something like a ","
    it wont completely solve the problem however it will still save alot of time

  9. #9
    Forum Contributor
    Join Date
    08-30-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: splitting text (address)

    then u need to learn this way. the exact way to do this problem. http://chandoo.org/wp/2008/09/08/spl...cel-functions/

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: splitting text (address)

    imram, you have maybe mis-understood the requirement from the OP. yes, that link will split the long text apart into it's separate parts (and is a very useful method to use), but that will not put a 2-word street name (or 2-word anything for that matter) into 1 cell, it will split it into 2 cells

  11. #11
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: splitting text (address)

    Hi -

    Do a Text To Columns for "," so atleast you can have the state and postcode separated and work on the other 3 columns manually.

    Regards,
    Event

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: splitting text (address)

    good suggestion, event21

    where is ph btw?

  13. #13
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: splitting text (address)

    FDibbins-

    ph=Philippines

  14. #14
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: splitting text (address)

    good suggestion on the ttc kabayan.

  15. #15
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: splitting text (address)

    vlady -

    proud to be Filipino

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: splitting text (address)

    lol oh ok, was just wondering about the .co.za (SA) addy

  17. #17
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: splitting text (address)

    FDibbins -

    haha, just my site has been hosted in SA with my accountant partner.

  18. #18
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: splitting text (address)

    After the text to column you can issue this one.

    =RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9}))+1) ->drag fill handle down....

    BUT as FD says
    the address could be a number (or not)
    so if there is no street number do it manually


    to explain further I've attached a file...


    string separate.xlsx


    Again just to shorten things up...
    Last edited by vlady; 12-13-2012 at 01:28 AM.

+ 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