Hi,
do someone know a VBA code to split a string in cell with commas, e.g. text ABCD should be A,B,C,D?
thanks a lot
Hi,
do someone know a VBA code to split a string in cell with commas, e.g. text ABCD should be A,B,C,D?
thanks a lot
Something like this:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
thank you very much
And if you have selected the cell - this also works
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
@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.
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
@ TMS,
Good one Trevor!
Only thing I would add is :
Regards.Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
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.
Last edited by xladept; 11-20-2018 at 03:54 PM.
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.
@ xladept,Please Login or Register to view this content.
Maybe you could provide for same with your Code?
Regards
Last edited by Winon; 11-20-2018 at 04:39 PM.
@ 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.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
@ 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.
Last edited by Winon; 11-20-2018 at 04:53 PM. Reason: Spelling!
@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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks