+ Reply to Thread
Results 1 to 7 of 7

Looking for formula to extract state names from free text cell... HELP!

Hybrid View

  1. 10-21-2014, 11:16 AM #1
    howardulson
    howardulson is offline
    Registered User
    Join Date
    10-21-2014
    Location
    NY
    MS-Off Ver
    Professional Plus 2010
    Posts
    2

    Unhappy Looking for formula to extract state names from free text cell... HELP!

    I have a spreadsheet with 12,000 rows of data regarding restaurants. From this data I need to create a column that shows what state each of these restaurants is located in. The problem is, the state name is buried in a free text field. Common text for this field will say "Located in shepherd county on route 29A in the state of Alabama. To get there take highway 46 west..."

    The text in each of these fields is different, but the state name is always included in there somewhere. Is there any formula I can use to extract the state name. I understand it may require creating a look-up table with all 50 state names, but after that I'm lost...

    Please help!

    Thanks,
    Howard-
    Reply With QuoteRegister To Reply
  2. 10-21-2014, 12:53 PM #2
    mcmahobt
    mcmahobt is offline
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Looking for formula to extract state names from free text cell... HELP!

    Can you post a sample workbook?
    Reply With QuoteRegister To Reply
  3. 10-21-2014, 01:16 PM #3
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Looking for formula to extract state names from free text cell... HELP!

    This can be accomplished by doing look up of the state names. For this you would need to create a list of states and use formula.

    Please see attached file with states names and a formula in B2
    Attached Files Attached Files
    Last edited by AlKey; 10-21-2014 at 02:51 PM. Reason: Updated formula on spreadsheet.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  4. 10-21-2014, 02:30 PM #4
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: Looking for formula to extract state names from free text cell... HELP!

    That's an excellent approach Alkey, but you need to be careful with states like Arkansas, which contains another state name (Kansas), so ideally the longer name needs to appear lower in the list than the shorter (like West Virginia and Virginia), otherwise you'll never get Arkansas returned......or if the state names will always have Upper case first letters you could use FIND instead of SEARCH
    Audere est facere
    Reply With QuoteRegister To Reply
  5. 10-21-2014, 02:46 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Looking for formula to extract state names from free text cell... HELP!

    Thank you DLL! This was an excellent catch. I didn't even think about it! Arkansas and Kansas))
    Reply With QuoteRegister To Reply
  6. 10-22-2014, 02:55 PM #6
    howardulson
    howardulson is offline
    Registered User
    Join Date
    10-21-2014
    Location
    NY
    MS-Off Ver
    Professional Plus 2010
    Posts
    2

    Talking Re: Looking for formula to extract state names from free text cell... HELP!

    Alkey and DLL. You guys are awesome. Thanks very much. Worked perfectly.

    Howard
    Reply With QuoteRegister To Reply
  7. 10-22-2014, 04:46 PM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Looking for formula to extract state names from free text cell... HELP!

    Thank you for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula not working to convert state names to abbreviations
    By moorea in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-30-2014, 04:18 AM
  2. [SOLVED] Extract State Code from Single Cell
    By maacmaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-11-2013, 10:14 AM
  3. How to get table names from access to free text?
    By Baker in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2012, 08:10 PM
  4. Extract Post Code data from a free text field
    By DGillham in forum Excel General
    Replies: 5
    Last Post: 07-05-2010, 03:38 AM
  5. [SOLVED] Extract city, state and zip code from a single cell
    By jajoseph@zoominternet.net in forum Excel General
    Replies: 6
    Last Post: 02-06-2005, 10:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1