+ Reply to Thread
Results 1 to 8 of 8

Split cell so that the last word in separate column

  1. #1
    Registered User
    Join Date
    07-02-2012
    Location
    Western Australia
    MS-Off Ver
    Office 2010
    Posts
    15

    Split cell so that the last word in separate column

    Hi,

    Basically I have lots of cells containing text such as:

    BROOME WA
    CENTENNIAL PARK NSW
    CABLE BEACH WA
    PORT MELBOURNE VIC

    etc...

    I want the state, E.g. the last "word" of the string, in another column. The state can be one of either WA, NT, NSW, ACT, VIC, QLD, SA, TAS.

    One problem that occurs, however is that on the odd occasion, someone has not entered the data correctly and the state has been omitted.

    This means every so often (500 cells or so) a cell contains text such as:

    CABLE BEACH
    BROOME

    etc...

    Is there a way that I can use a formula to check if the cell contains a state, if not, leave it. If it does contain a state, separate it from the rest of the cell. If there is a state it will always occur at the end of the string, however a string can be from 1 ~ 5 words.

    Any help is greatly appreciated and Rep will obviously be given to anyone that helps solve the problem

    Cheers,
    --CameronP

    ---------- Post added at 12:07 PM ---------- Previous post was at 11:09 AM ----------

    Anyone at all have an idea?

    I need this for work and I am stumped, been trying to figure it out with LEFT, RIGHT and MID functions among others but I am really stuck.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Split cell so that the last word in separate column

    Hi CameronP,


    You can use below formula to obtain the last word:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    last word extraction.xlsx

    Is there a way that I can use a formula to check if the cell contains a state, if not, leave it. If it does contain a state, separate it from the rest of the cell.
    For this, I would need a list of states

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    07-02-2012
    Location
    Western Australia
    MS-Off Ver
    Office 2010
    Posts
    15

    Re: Split cell so that the last word in separate column

    Hi Dili,

    Thanks for the reply. The sheet you attached works wonderfully, however when I try to replicate the formula on other sheets it is not working properly.

    That doesn't bother me as I can copy/paste the data into your sheet and back out again once processed.

    Also, is there a way to remove the state once it is copied across? I require the state and suburb in separate columns and even if I copy the state I still need to find a way to then remove it from the original column. If you can help with that, I would appreciate it. Also, I will try adding an IF(ISBLANK()) in so that it doesn't return a #value! error when it comes across one of the many empty cells.

    Thanks for that,

    CameronP

    ---------- Post added at 12:56 PM ---------- Previous post was at 12:54 PM ----------

    Also, the states are as I listed in my first post, but here they are again:

    They can only be one of:

    WA, NT, SA, QLD, NSW, ACT, VIC, TAS

    Cheers,

    ---------- Post added at 12:57 PM ---------- Previous post was at 12:56 PM ----------

    And one more thing.

    I coppied all my data (4900 lines) into your formula and it splits some but not others. Any ideas?

  4. #4
    Registered User
    Join Date
    07-02-2012
    Location
    Western Australia
    MS-Off Ver
    Office 2010
    Posts
    15

    Re: Split cell so that the last word in separate column

    last word extraction2.xlsx

    That sheet contains ALL the cells that need splitting. As you can see, the formula has only applied to a certain section of cells. :\

    Any ideas? Cheers.

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Split cell so that the last word in separate column

    find a way to then remove it from the original column.
    you can use substitute function for this.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also, the states are as I listed in my first post, but here they are again:
    Taken care of



    I coppied all my data (4900 lines) into your formula and it splits some but not others. Any ideas?
    Check the references and try finding out the difference why it worked for rest

    Cheers

    see attached:-
    last word extraction.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

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

    Re: Split cell so that the last word in separate column

    Hello CameronP,
    you should also look at this list of state i think your missing one. Just include on dilip's lists of states.

    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

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Split cell so that the last word in separate column

    Good point vLady

    cheers

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

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

    Re: Split cell so that the last word in separate column

    Last edited by vlady; 08-03-2012 at 02:13 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