split string in cell

Something like this:

Please Login or Register  to view this content.

And if you have selected the cell - this also works

Please Login or Register  to view this content.

@xladept: not sure there's much difference. Your solution will fail if an empty cell is selected. I know, because mine did and I fixed it.

NVM misread.

@ TMS,

Good one Trevor!

Only thing I would add is :

Please Login or Register  to view this content.

Regards.

Hi Trevor,

I was hoping the OP would more definitively define(!) the object Cell.

But you're right and I have "fixed" it

Orrin

Please Login or Register  to view this content.

It just occurred to me that what if the Activecell contains a number off leading and/or trailing Spaces, which are not visible, and which none of the Code solutions addresses.

I have taken the liberty to "Fix" TMS's Code to handle such cases, if any.

Please Login or Register  to view this content.

@ xladept,

Maybe you could provide for same with your Code?

Regards

@ Winon - I already did that

An alternative solution if you have a very large data set would be to use Power Query.

Here is the MCode

Please Login or Register  to view this content.

@ xladept,

For some reason, if the cell contains only spaces, it bugs out on this line of your Code;

Please Login or Register  to view this content.

@ alansidman,

You have totally lost me due to my ignorance of Power Query thus far.

Could you please provide a sample Workbook of same for better understanding?

Thank you.

@Winon: if the cell contains leading or trailing spaces, why would you assume they're not wanted?

And you are testing and trimming the ActiveCell AFTER I set cell.

For those "spacey cells":

Please Login or Register  to view this content.

@ TMS,

Thank you for your response.

I apologize if you feel that I should not have interfered with your Code, and I shall gladly remove it.

Your original Code as offered in Post # 2 will then remain as is, and return the following sample Screenshot.


Regards.

@Winon: no apology necessary. More a comment than a criticism. Tinker with the code as you wish. With only one trivial example, who knows exactly what the OP is expecting?

@Winon
Attached file

For anyone interested, there is a great book that makes this very simple to understand.
M is for (Data) Monkey by Ken Puls and Miguel Escobar.

@ alansidman,

Thank you so much for your effort and time. I really appreciate it!

Best regards.