Yes, I see the problem. But to understand the solution, you must look at the first few rows of Sheet2 and understand why they work; then you will be able to look at the following rows and understand why they don't work.

In my solution, I use three functions: FIND, LEFT and MID. Do you know what those functions do? If you don't, tell me and I will explain them. If you do—when you do—you can then study what I did in rows 1 through 4 and see how each piece of that solution works.

When you understand that, you will probably understand what you must do differently with your own data. But I will still help you with that, too. First understand what I'm doing in rows 1 through 4. Tell me how much of that you understand, and I'll explain from there.