Wise Owl Answers - How do I split text into multiple rows in Excel VBA?

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

[grzegorz]

Man, u are the best. i have bought ur yt subscription and after seeing this video, I 100% don't regret that. HUGE THANKS

[dineshanblazahan]

Great video, I think ur born to teach … hats off to your teaching skills

[AmitSharma-pozb]

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..👍👌

[krn]

Thanks Andrew. Love the FIND/FIND NEXT

[udani]

Thank you very much Andrew! Useful as always! Found the answer for what i have been looking for..pls keep up the good work☺️👍🏻

[shahir]

Many thanks to this video.
Very much helped...

[francis]

thanks a lot that made my life very easy as I was starting to scratch my head :D

[RRakshithR]

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.

[KhalilYasser]

Thank you very much. Awesome and amazing.

[rajanlal]

Very nice, it's really helpful.. Thanks a lot...

[frikduplessis]

Thank you Andrew what an great example, this is a split function on steroids 😀👏👏

[PankajNegi]

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.]

[jillanis]

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 (“+”, ”-”, ”*”, ”/”)

[serenahu]

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?

[deepali]

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.

[vineetat]

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 😊

[montezuma]

How would this work with a character limit instead of a symbol?

[akshaay]

Thanks a lot, very helpful. Could you please provide the code ? Thanks.

[flyerful]

what if the "/" changes into "alt+enter"?
Can you please solve that?