+ Reply to Thread
Results 1 to 14 of 14

Looking for help to my question, taking words from a string

  1. #1
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2003
    Posts
    14

    Looking for help to my question, taking words from a string

    Hi all i am currently working on improving things at the work place and want a specific formula.

    The scenario is many depots enter information through a software called K8 which means they can type in anything and is not conistent as there are hundreds of depots, so lots of different people will be entering information. I export the data and then have to manually type each depot (location)

    I want to be able to take certain words out of the text they type in in relation to another list.

    For example....

    depot 1 - 200 cash banbury
    depot 2 - cash for radcliffe
    depot 3 - Oak hall cash transfer

    I want to take out the locations only, is there a way i can use the list of depots 600+ to look up the string entered by the depot and extract only the word I am looking for, The LEFT and MID functions would not work as the location can be anywhere in the text.


    Any help?

    I hope i have explained well enough.

    THANKS!!!!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Looking for help to my question, taking words from a string

    Please post a file with sample data (no confidential data), identifying depots and the data you require.

    To post a file click "Go Advanced" then "Manage Attachments".

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking for help to my question, taking words from a string

    What version of Excel does this have to work in?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2003
    Posts
    14

    Re: Looking for help to my question, taking words from a string

    There is an example


    Thanks
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2003
    Posts
    14

    Re: Looking for help to my question, taking words from a string

    2003 is the latest at work

    Thanks

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking for help to my question, taking words from a string

    Your data setup isn't conducive to easy analysis!

    This formula entered in E5:

    =IFERROR(LOOKUP(1E100,SEARCH(G$3:G$10,B5),G$3:G$10),"")

    EDIT: This formula requires Excel 2007 or later.
    Last edited by Tony Valko; 01-16-2016 at 04:34 PM.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking for help to my question, taking words from a string

    Quote Originally Posted by edmondsr View Post
    2003 is the latest at work
    So which version should we write the formula to work in?

  8. #8
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2003
    Posts
    14

    Re: Looking for help to my question, taking words from a string

    2003 please.


    Thanks very much!

  9. #9
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2003
    Posts
    14

    Re: Looking for help to my question, taking words from a string

    just out of interest would you be able to explain what that clever formula does?

    Thanks

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking for help to my question, taking words from a string

    OK, this one will work in any version of Excel:

    =LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH(G$3:G$10,B5),G$3:G$10)))

  11. #11
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2003
    Posts
    14

    Re: Looking for help to my question, taking words from a string

    thats great thanks a lot

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking for help to my question, taking words from a string

    You're welcome!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  13. #13
    Registered User
    Join Date
    01-16-2016
    Location
    London
    MS-Off Ver
    2003
    Posts
    14
    Quote Originally Posted by Tony Valko View Post
    You're welcome!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
    Will do that now! Can you explain the method briefly ?

    Thanks again

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking for help to my question, taking words from a string

    Been offline for the last week.

    Still need an explanation?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 15
    Last Post: 10-12-2014, 08:53 AM
  2. [SOLVED] Extract all the words from a string for processing, while leaving string intact.
    By staggers47 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-10-2014, 02:02 PM
  3. Replies: 1
    Last Post: 07-02-2014, 12:02 PM
  4. [SOLVED] Taking Text From 1 End Of A String And Putting It On The Other
    By Tellm in forum Excel General
    Replies: 3
    Last Post: 07-04-2012, 08:20 AM
  5. [SOLVED] Taking an integer out of a string
    By Mike112 in forum Excel General
    Replies: 13
    Last Post: 06-01-2012, 02:46 PM
  6. Formula for taking out words in cell required
    By CC_mfc in forum Excel General
    Replies: 2
    Last Post: 01-23-2007, 08:20 AM
  7. Taking words and creating a value
    By fednick in forum Excel General
    Replies: 5
    Last Post: 11-16-2006, 05:57 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1