How to split text in excel - 7 methods

Flash Fill, Text-to-Columns, Power Query, Formula, Macro, Google sheets, UDF
Separate words into multiple columns
Method 1: Flash Fill 00:00
Method 2: Text-to-Columns 00:39
Method 3: Power Query 01:17
Method 4: Formula 02:40
Method 5: Macro 03:01
Method 6: Google sheets 09:44
Method 7: UDF 10:30
--------------------------------------------------------------------------------------------------------------
=TRIM(MID(SUBSTITUTE($J5," ",REPT(" ",LEN($J5))),(COLUMNS($J4:J4)-1)*LEN($J5)+1,LEN($J5)))
--------------------------------------------------------------------------------------------------------------
Sub splt1()
Dim r As Integer
Dim c As Integer
r = ActiveCell.Row
c = ActiveCell.Column
Selection.TextToColumns Destination:=Cells(r, c + 1), DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Space:=True
End Sub
--------------------------------------------------------------------------------------------------------------
Function splt(rng As Range, dlim As String, num As Integer)
With Application
splt = .IfError(.Index(Split(rng, dlim), 1, num), "")
End With
End Function

[teddyalens]

Thank you Karina, this is a great tutorial for a new guy in excel and everything is explained in detailed.

[querrythis]

You make some difficult things look simple. Thanks a lot for your help.

[ahmedaiad]

great method
you 've just acknowledged me that making a new customized function is easy and possible

[cindylam]

Your presentation is very clear . Thank you very much . It helps me .

[vaibhavsingh]

Life saviour. Really amazing trick. Thanks a lot.

[vishnu]

This is amazing. I had a simple task but nonetheless very useful! Bookmarking this video

[thewelcometodark]

Thank you so much I was looking for Google sheet method. It helped me lot thank you madam.

[manjunathmahamuni]

Wow great tips Thank you very much for this video...

[blitz]

Hello, I currently have to split names from cells that are presented in this format: STEVENS/JOHNSON/JOHN MARK into 4 separate columns but using formulas instead of text to columns, and they have to be presented as: STEVENS JOHNSON JOHN MARK in the 4 separate columns, would any of these work for that purpose?

[zohurulislam]

This video is
Great!
Awsome!!
And
Making speechless!!!!
Thank you
Thank you so much.

[pjyruik]

Thanks video!
rng change to variant can nested used then u can select u wanted in two dlim

[scottnaples]

This is a great tutorial. I love how in your description you put a link to the time of the video for each method. Great work. I ultimately used Method 4 which worked perfectly, but would love to see the video/ explanation for that. Do you ultimately create a video tutorial for that? If so, can you provide a link?

[filipchobodicky]

Great job Karina. Thanks for the ispiring

[MayteLoveMetal]

Really well explained! thanks a lot :)

[sojolhamid]

Ma'am, I want to split "PointA-PointB" this text by "-" & using only excel function, how can I do it? Could you please share the exact function, so that one column will get "PointA" & next column will get "PointB". I don't want to use "Text to column" option. I want to use only formula. How can I do it? could you please share the formula text? Thanks in advance.

[danfedsadventures]

Hello,
Do you have the video uploaded explaining the formula? Cheers

[abdulsomediddrisu]

Flash fill method got me subscribed to your channel.

[Notinuse]

I only want it to separate the text with the first space is there a way to do this ?

[vpin]

Thank you for this great video. I could use your help to split a lengthy line of text (copy a paragraph from an article into a cell. It is difficult to read without scrolling) into multiple lines (rows)

[TrentGustus]

I can't seem to delimit text more than 5 columns.. it just deletes the rest in "text to columns" is this a glitch?