# HELP! Missing Text when I use the REPLACE Formula

#### WitkosRobinson2

##### New Member
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>

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
What is in W46 & Y46?

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

This will work with the example above.

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

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>

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

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

OK test this one

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

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!!

You're welcome.

Replies
1
Views
244
Replies
9
Views
481
Replies
3
Views
182
Replies
11
Views
634
Replies
2
Views
99

1,202,901
Messages
6,052,441
Members
444,581
Latest member
naninamu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back