+ Reply to Thread
Results 1 to 13 of 13

Extract all text to the left of a hyphen ???

  1. #1
    Forum Contributor
    Join Date
    09-07-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    100

    Talking Extract all text to the left of a hyphen ???

    im wondering how to extract all information to the left of a hyphen in cell A1 and place it in cell A2. Please see example.

    cell A1 reads,

    West Bromwich - Manchester United

    I want A2 to read,

    West Bromwich

    Cheers, burdo.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,174

    Re: Extract all text to the left of a hyphen ???

    Use this:

    =LEFT(A1,FIND("-",A1)-1)

    To get the other part:

    =RIGHT(A1,LEN(A1)-FIND("-",A1))

    You might like to wrap TRIM( ... ) around those to remove any extra spaces that you don't need.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    09-07-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Extract all text to the left of a hyphen ???

    Hey pete, thanks.

    I'm glad u posted the right cause that would of been my next question.

    Cheers Josh.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,174

    Re: Extract all text to the left of a hyphen ???

    Glad to help.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  5. #5
    Forum Contributor
    Join Date
    09-07-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Extract all text to the left of a hyphen ???

    first solution worked however I have another snag.

    B2 reads West Bromwich

    i want to lookup same text as B2 within the column running from B4:B24 to find West Bromwich and extract the number within that row.

    Cheers burdo

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,174

    Re: Extract all text to the left of a hyphen ???

    What the formula actually returns is West Bromwich with a space at the end - you should use:

    =TRIM(LEFT(A1,FIND("-",A1)-1))

    to get rid of any extra spaces, and then this should match directly (if you are using VLOOKUP, for example). It that doesn't solve your problem, please attach a sample workbook (the FAQ describes how to), to explain what you want to achieve.

    Hope this helps.

    Pete

  7. #7
    Forum Contributor
    Join Date
    09-07-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Extract all text to the left of a hyphen ???

    Hi Pete,

    It didn't really help. I have attached my file, please see my red text box at the top of sheet 2 for instructions.

    Cheers Burdo
    Attached Files Attached Files

  8. #8
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,257

    Re: Extract all text to the left of a hyphen ???

    Hope it helps, you must have same criteria with your lookup table, like this one....

    Criteria: Manchester United

    but in your table: Manchester Utd

    Yes it can be done with SEARCH or FIND Function but you have other similar team, like : Manchester City or more like, and your data in every team is unique, so its difficult to retrieve the results.. so the easy way is changing word "utd" with "United"

    Cheers
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    09-07-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Extract all text to the left of a hyphen ???

    you are right azumi, i will change it and seee how i go.

    Cheers burdo

  10. #10
    Forum Contributor
    Join Date
    09-07-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Extract all text to the left of a hyphen ???

    ok i have changed to the exactly the same text and trimed the text, however i still dont know how to match the data with the text.

    Instructions are on page 2 of my file
    Attached Files Attached Files

  11. #11
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,257

    Re: Extract all text to the left of a hyphen ???

    Please see the file


    Cheers
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    09-07-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    100

    Talking Re: Extract all text to the left of a hyphen ???

    OK azumi you have nailed it!

    thanks for your help.

    Cheers burdo

  13. #13
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,257

    Re: Extract all text to the left of a hyphen ???

    You're welcome, happy to help....

+ 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. Extract text left and right of character part 2
    By stuartm4h in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-01-2013, 07:12 AM
  2. [SOLVED] Extract text left and right of character
    By stuartm4h in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2013, 07:32 AM
  3. extracting text left of second hyphen
    By nikoelnutto in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2012, 10:44 AM
  4. extract numbers with specific text from right or left
    By darkhangelsk in forum Excel General
    Replies: 2
    Last Post: 08-15-2009, 02:38 PM
  5. Finding first hyphen from the left
    By matpj in forum Excel General
    Replies: 3
    Last Post: 04-17-2007, 11:47 AM

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