Formula Help - Removing Everything to the right of final hyphen

**zippyfrog** · 08-20-2012, 11:01 AM

Hi, I am new to this forum and I am hoping that I have some excel experts here that can help with a formula issue I am having. What I want to do is remove everything to the right of a hyphen from a field, but the issue is that some fields have multiple hyphens.

How would I go about doing this?

Here is a sample of what I am looking to do.

Web Work - 1
Web-Work - 1
Lots-Of-Web-Work - 7

I would like to make

Web Work
Web-Work
Lots-Of-Web-Work

Basically, I want to remove everything to the right of the last hyphen in the string, including the hypen itself. All the searches I have found online remove everything after the first hyphen or a specific numbered hyphen in the string. My issue is that different rows have different number of hypens. Formulas such as =LEFT(A2,FIND("-",A2)-2) just aren't cutting it. I would love to have a formula where I can just click and drag down in a separate column to create this new string, as I have some 4000 lines that I need to remove the back hyphen for, and everything after it.

Thanks in advance for any help you can provide!

**JosephP** · 08-20-2012, 11:13 AM

=TRIM(LEFT(A2,FIND("~~",SUBSTITUTE(A2,"-","~~",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1))
for instance

**zippyfrog** · 08-20-2012, 11:43 AM

Thank you so much! That is a huge help!

**Cutter** · 08-20-2012, 11:57 AM

Hello zippyfrog, and welcome to the forum.

Thanks for changing your title.

**zippyfrog** · 08-20-2012, 07:37 PM

Sorry about that. I hope the new title is appropriate.

**Cutter** · 08-20-2012, 09:25 PM

@ zippyfrog

Based on your previous post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.