+ Reply to Thread
Results 1 to 7 of 7

extracting characters from string

  1. #1
    Forum Contributor
    Join Date
    09-19-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    140

    extracting characters from string

    Hey all,

    I know this is possible because I had it working but saved as CSV and lost all my formulas. And now can't get it working again.

    I would like to extract the first two characters after the last instance of a space in its own column if the rightmost character contains 7 digits ( hence FL will be in its own column). If the length of characters after last space does not contain 7 characters, then that means it's Canada and I would like to put the first two characters (CA) in an entirely separate column that contains the states. This would be the country column.

    WEST PALM BEACH FL33416
    FORT LAUDERDALE FL33309
    RAHWAY NJ07065
    ORLAND PARK IL60467
    VAUGHAN ON CAL4K 1


    Thanks for any response.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting characters from string

    Try this
    For USA in B2
    Please Login or Register  to view this content.

    For Canada in C2
    Please Login or Register  to view this content.

    Hope this helps
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: extracting characters from string

    presumably, Marcol, the OP would combine the two:
    =IF(ISNUMBER(RIGHT(A2,5)*1),LEFT(RIGHT(A2,7),2),"CA")
    ?
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting characters from string

    Not if as the OP says
    .....then that means it's Canada and I would like to put the first two characters (CA) in an entirely separate column that contains the states.
    This might be what is needed for Canada, if the Canadian code is always the same length
    Please Login or Register  to view this content.

    rather than
    Please Login or Register  to view this content.

    Hope this helps
    Last edited by Marcol; 10-12-2010 at 12:49 PM.

  5. #5
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: extracting characters from string

    oh... yeah... easy when you actually read it.

  6. #6
    Forum Contributor
    Join Date
    09-19-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    140

    Re: extracting characters from string

    Thanks for replies.
    This worked:
    Please Login or Register  to view this content.

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: extracting characters from string

    On behalf of all, happy to have helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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