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.

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

Hey pete, thanks.

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

Cheers Josh.

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

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

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

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

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

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

Cheers burdo

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

Please see the file


Cheers

OK azumi you have nailed it!

thanks for your help.

Cheers burdo

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