Hello Sreek,
Sorry, man! I can't follow your explanations.
My code doesn't put any semicolons anywhere! This is what it does:
[*] Copy the original, semicolon-delimited string from 'Original' to 'Automatic Result'.
[*] Split the strings at the semicolons
[*] Write each element in a separate row
Now, If you were to throw the 'Original' away after this, I believe that the result would be rather similar to what you describe except you say that you copy from column A to column D and then insert semicolons. Note the difference:
[*] You copy from A to D, I copy from Original!A to 'Automatic Result'!A.
[*] You insert semicolons in column D. I expect the semicolons to have been inserted in column A.
At the top of my code sheet (Module1) there is a constant called DataCol. Its value is "A", meaning that data will be picked from Original!A. If you change this value to "D" data would be picked from Original!D. So, if your client sends you the sheet with his list in column A you could copy it to D, insert your semicolons and then run my macro using the data from D. You would then see the data from Original!D in 'Automatic Result'!A.
I can't be of more help becasue I don't understand where the semicolons bother you, since I don't introduce any to the equation. I just use the ones you provide. Perhaps you have another look at the sheet "Manual Result". This is the sample that I have based myself on. If you need something different, perhasp this is the place to describe it.
I asked you about the availability of semicolons as separators and you didn't respond. You should have. Now note the following:
[*] It is very easy to change the separator to any character you want. If your clients use semicolons in the criteria occasionally, you might take the ampersand (&) or the pound (#) or the backslash (\) or any character they never use. This is a truly minor change you need not worry about.
[*] If your clients use different separators but each cell always has the same, we might relieve you of the manual task of setting many separators. I can imagine a little function that replaces the separator used by the client with the separator needed by the macro. Sort of, select the cell, activate the macro (perhaps by double-click), the macro ask you which separator the client used (perhaps we find a way for the macro to find out by itself), the altered string is pasted to column D (leaving the original intact).
Let me know how I can help you.