Excel Replace/Substitute Problem

Hi Everyone!
I have some content in excel cell. I want to find a certain string and replace left part of that string from that cell including that string. If you see the snapshot then you will easily understand the problem.

I have also attached the sample data in excel. Let me know if that is something we can achieve.

Thanks in advance.

excel forum Snapshot_2.PNG

You can do this with a formula but it has to be converted to an .xlsx file to have formulas. The idea is to use FIND to locate the snippet, and then get the MID substring starting after that snippet.

Please note that in your first column, the text does not exactly match what is in the second column. The second column has extra spaces between all the words so it won't match. I have fixed the first column in the attached example so it will work. Also the second column has line breaks that are not in the first column. I have also added those so it works.

On row 3, your example Result has text that does not exist in the original content.

BTW I agree about Conti. I have one of his recordings and one of his instructional videos and it's just like he's showing off how great he is instead of teaching you anything. And "Death by Chops" is just a ridiculous tune--his time has hiccups. He is a good player, but not an artist.

Originally Posted by anonymous321

Hi, I have pasted some data in your provided sheet but it the formula doesn't work. May be I am doing some mistake. Please me know about your thoughts.
Attachment 709607

I can see you tried to attach a file but it is invalid. To attach a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
3. Click the Choose File button to select a file to attached.
4. Click the Upload button to upload the file.
5. Then click the Close This Window button. Your file is now attached to your post.

I have updated my previous reply with attachment. Please have a look now.

Originally Posted by anonymous321

Hi, I have pasted some data in your provided sheet but it the formula doesn't work. May be I am doing some mistake.

As I explained when I provided the solution, the text in column A does not match the text in column B. The text in column B has extra spaces between the words.

Hi, the formula works on most of the part of data. If you look the data there are some errors in the data. Please relook the excel sheet you provided. And thanks for your effort.

Originally Posted by Bo_Ry

There are double space in the sentense
Please try this at D2 to keep double space

Formula:

Please Login or Register  to view this content.

or at E2 to remove double space
=MID(TRIM(B2),SEARCH(A2,TRIM(B2))+LEN(A2),6^6)

Hi, the formula works on most of the part of data. If you look the data there are some errors in the data. Please relook the excel sheet you provided. And thanks for your effort.