HELP! Missing Text when I use the REPLACE Formula

[WitkosRobinson2]

When I use the below formula its missing ' - Test' at the end. See row 1 for results and row 3 for what I need it to be. Any ideas on how I can amend the below to fix this?

=Y46&" "&REPLACE(LEFT(W46,FIND("-",W46,FIND("-",W46)+1)-1),1,FIND("-",W46),"")

WL - PAs - Test-27/01/2012	WL-Test - CA	WL-Americas - CA - PAs
IT SHOULD BE
WL - PAs - Test-27/01/2012	WL-Test - CA	WL-Americas - CA - PAs - Test

<tbody>
</tbody>

 

[gaz_chops]

What is in W46 & Y46?

 

[WitkosRobinson2]

So, sorry!! Does this help!

W46 = WL - PAs - Test-27/01/2012

Y46 = WL - Test - CA

Column Z is where I want the result = WL-Test - CA - PAs - Test ( I am made an error is my text above)

but it's giving me

WL-Test - CA - PAs

 

[gaz_chops]

This will work with the example above.

=Y46&MID(W46,FIND("-",W46)-1,13)

 

[WitkosRobinson2]

It's giving me two '- -' before 'PAs' and it should only give me 1 WL-Americas - CA - - PAs - Test <tbody> </tbody> For longer ones it chops the text, is there a way for the formula to FIND '-' before DATE? '-12/01/2012' then STOP. W16 = WL - Lawyers - Litigation-12/01/2012 Y16 = WL-Test - CA - <tbody> </tbody>

<tbody>
</tbody>

Result in column Z

WL-Test - CA - - Lawyers - (=Y16&MID(W16,FIND("-",W16)-1,13) (I know I can change the 13 depending on characters but it's very manual.

<tbody>
</tbody>

 

[gaz_chops]

Can you post a few more examples of before and after, is the text you want always between the 1st and 3rd hyphens?

 

[WitkosRobinson2]

Here are a few more examples, the issue seems to be the second '-' after 'Mining PA' for example. I need the text string to stop before '-27/01/2012'. Does that help, I can't thank you enough for trying to help me. I have 1000s of this to fix

WL - Mining PA - Teck Testing-27/01/2012	WL-Test - CA -	WL-Test - CA - Mining PA
Should be
WL - Mining PA - Teck Testing-27/01/2012	WL-Test - CA -	WL-Test - CA - Mining PA - Teck Testing
WL - Lawyers - Employment-12/01/2012	WL-Test - CA -	WL-Test - CA - Lawyers
Should be
WL - Lawyers - Employment-12/01/2012	WL-Test - CA -	WL-Test - CA - Lawyers - Employment
WL - Lawyers - Litigation-12/01/2012	WL-Test - CA -	WL-Test - CA - Lawyers
Should be
WL - Lawyers - Litigation-12/01/2012	WL-Test - CA -	WL-Test - CA - Lawyers - Litigation

<tbody>
</tbody>

W45 = WL - Mining PA - Teck Testing-27/01/2012
Y45 = WL-Test - CA -
Results in column Z45 = WL-Test - CA - Mining PA
Should be
W45 = WL - Mining PA - Teck Testing-27/01/2012
Y45 = WL-Test - CA -
Results in column Z45 = WL-Test - CA - Mining PA - Teck Testing


W15 = WL - Lawyers - Employment-12/01/2012
Y15 = WL-Test - CA -
Results in column Z15 = WL-Test - CA - Lawyers
Should be
W15 = WL - Lawyers - Employment-12/01/2012
Y15 = WL-Test - CA -
Results in column Z15 = WL-Test - CA - Lawyers - Employment


W16 = WL - Lawyers - Litigation-12/01/2012
Y16 = WL-Test - CA -
Results in column Z16 = WL-Test - CA - Lawyers
Should be
W15 = WL - Lawyers - Litigation-12/01/2012
Y15 = WL-Test - CA -
Results in column Z15 = WL-Test - CA - Lawyers - Litigation

 

[gaz_chops]

OK test this one

=Y15&MID(W15,FIND("-",W15)+1,FIND("§",SUBSTITUTE(W15,"-","§",3))-1-FIND("-",W15))

 

[WitkosRobinson2]

Hello gaz_chops, I wanted to thank you for looking into my problem for me!! I am happy to report it worked like a dream and you saved me hours!! Thanks again!!

 

[gaz_chops]

You're welcome.