I'd really recommend you learn the TEXT to COLUMNS feature. It's designed to do exactly what you are trying to do.

I was finally able to duplicate your results. Your sample formula above didn't match what you said you did. I had to change it to =LEFT(A2,LEN(B2) *5) which I placed in C2.

So what's happening is the LEN function in C2 looks at the number of characters in the answer to LEN(A2). In your sample, this function returned 120, which results in 3 characters. Use 3 * 5 and it pulls the first 15 characters from A2. Use 1, and it pulls 3 characters. You might as well hard code the number of characters for your LEFT function. It's easier and results in the same thing.

Your sample data isn't clear to me. Is the word MERCHANT in every row, or is it just in the first row as the column header?

If in every row, you can use the formula I created. If not, there are ways using formulas to search for the 1st and 2nd space characters to return only the Business name.

Again, use TEXT to COLUMNS. It's meant to do this and takes seconds once you figure out which settings to use for this data. I'd recommend starting with "space" as your delimiter.