Wise Owl Answers - How do I split text into multiple rows in Excel VBA?
Check out our other answers here • Wise Owl Answers
Get files here www.wiseowl.co.uk/vba-macros/...
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
This video shows you how to use the Split function in VBA to divide a string into multiple substrings based on a delimiter character. You'll also see how to capture the result of the Split function in an array and write the contents of the array into cells in a worksheet after inserting new rows to make space for the new values.
Chapters
00:00 The Question
00:42 The Basic List
01:36 Splitting a String
03:51 Counting Through an Array
05:23 Writing Array Values into Cells
06:55 Resetting the Worksheet
08:24 Inserting Rows into the Worksheet
09:31 Resetting the Worksheet
10:54 The Find and FindNext Methods
16:22 Avoiding Endless Loops with FindNext
18:00 Finishing Touches
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Power BI, DAX, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access and more
Пікірлер: 59
Man, u are the best. i have bought ur yt subscription and after seeing this video, I 100% don't regret that. HUGE THANKS
@WiseOwlTutorials
2 жыл бұрын
Thank you for the support, I'm pleased to hear that you found it useful!
Great video , I think ur born to teach … hats off to your teaching skills
@WiseOwlTutorials
2 жыл бұрын
Thanks Dinesh!
Many thanks to this video. Very much helped...
@WiseOwlTutorials
2 жыл бұрын
Happy to hear that Shahir, thanks for watching!
Thank you Mr. Andrew 🤝👍👍
@WiseOwlTutorials
3 жыл бұрын
You're very welcome Sim!
thanks a lot that made my life very easy as I was starting to scratch my head :D
@WiseOwlTutorials
2 жыл бұрын
Happy to hear that it helped, Francis. Thanks for watching!
truly lovely indeed magnificent code
@WiseOwlTutorials
Жыл бұрын
Glad you liked it, thanks for watching!
Very nice, it's really helpful.. Thanks a lot...
@WiseOwlTutorials
Жыл бұрын
You're welcome, thanks for watching!
Thanks Andrew. Love the FIND/FIND NEXT
@WiseOwlTutorials
3 жыл бұрын
Cheers Kevin, glad you liked it!
Thanks a ton Andrew for highlighting me ☺️..May the viewers have another part to this video which will show how to then auto fill the data of the respective columns in the blank cells below of each column after the rows are created with the individual strings..I suppose that will be a super informative as well..Kudos to your great work and in-depth knowledge..👍👌
@WiseOwlTutorials
3 жыл бұрын
You're very welcome Amit, thank you for the question! I'll add the second part of your question to the list but it may be some time before I get around to covering that in a new video. Happy to hear that you like this one and thank you for your support!
@AmitSharma-po1zb
3 жыл бұрын
Thanks a ton to you Andrew for acknowledging our queries..👍👌😊
@jjewel6812
Жыл бұрын
I was wondering if you got the solution of your question about the auto fill???
@AmitSharma-po1zb
Жыл бұрын
@@jjewel6812 yes I did
@jjewel6812
Жыл бұрын
@@AmitSharma-po1zb Could you provide me with the code? I am workin on a similar thing. Would be very grateful
Thank you very much Andrew! Useful as always! Found the answer for what i have been looking for..pls keep up the good work☺️👍🏻
@WiseOwlTutorials
2 жыл бұрын
Excellent! Happy to hear that the video helped and thank you for watching!
Thank you very much. Awesome and amazing.
@WiseOwlTutorials
3 жыл бұрын
You're very welcome Yasser!
Thank you awsome solution
@WiseOwlTutorials
2 жыл бұрын
You're very welcome Inam!
Thank you Andrew what an great example, this is a split function on steroids 😀👏👏
@WiseOwlTutorials
3 жыл бұрын
😀 thanks Frik! Glad you enjoyed that one!
Awesome video 👍.. Thank you bro!
@WiseOwlTutorials
3 жыл бұрын
You're welcome Prasad, glad you enjoyed it!
Thank you
@WiseOwlTutorials
3 жыл бұрын
You're welcome Rajesh, thank you for watching!
Great 👍
@WiseOwlTutorials
3 жыл бұрын
Thanks Panner!
Thanks alot Andrew, your explaination really helped in solving my problem. Can you also help for the problem statement - "if column rank and Year data also needs to be updated for the rows that are divided from original row". Thanks in advance.
Thanks you so much this is much needed one, can you please tell me how to split with multiple criteria for example instead of “/” with array like (“+”, ”-”, ”*”, ”/”)
@WiseOwlTutorials
3 жыл бұрын
Hi Jillani! There's a nice discussion of this topic here with a few different solutions stackoverflow.com/questions/7680490/how-to-split-a-string-with-multiple-delimiters-in-vba-excel It seems like the easiest approach is to use the Replace function to convert all of the delimiters into the same character and then use the Split function using the one delimiter character you have left. I hope that helps!
Thanks for this. But after the split, if I want to copy the other columns e.g. title into the newly inserted row, how to do?
Great Andre Sir, I enjoy another training session on VBA which is Masterpiece. Sir a humble request to you 'what is the best way to use any formulas/Functions in Excel using VBA with Loop Like Vlookup/Countifs/Sumifs etc. [when i want to use these functions I record.]
@WiseOwlTutorials
3 жыл бұрын
Thank you Pankaj! If you want to use Excel functions in your code you can use the WorksheetFunction object. You can see a full list of which functions it supports here docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction and there are links to the specifics for each function such as this one for VLookup docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.vlookup I hope that helps!
@PankajNegi007
3 жыл бұрын
@@WiseOwlTutorials thanks Sir for prompt Reply
hey, if i want to split the text and also the entire row will lso be copied i.e. action and adventure will also have rank, title, worldwide gross,year same as science fiction(row1). Please Help me with this.
How would this work with a character limit instead of a symbol?
Hi Andrew, Can you record your VBA trainings once again since we have Ms 365 now. I hope this will help you and your students like me 😊
@WiseOwlTutorials
3 жыл бұрын
Hi Vineet! Thanks for the suggestion. We have updated versions of the basic VBA videos which you can find in this playlist kzread.info/head/PLNIs-AWhQzckV9rAM3yv8ym4pioIMA0UR It's very unlikely that we'll remake all our VBA videos because 1) the VBA code in the older videos still works in modern versions of Excel, and 2) we have hundreds of VBA videos and it would take a huge amount of time to remake them which would prevent us from making new content.
@vineetat2
3 жыл бұрын
@@WiseOwlTutorials That's Great. Thanks.
Thanks a lot, very helpful. Could you please provide the code ? Thanks.
@WiseOwlTutorials
2 жыл бұрын
Hi! Check the video description!
what if the "/" changes into "alt+enter"? Can you please solve that?
@viniciusmartins5409
2 жыл бұрын
Chr(10)
How do I get the vba code?
@WiseOwlTutorials
2 жыл бұрын
Hi! There's a link in the video description, I hope it helps!