The Best Way To Split Text In Excel

In this video, you can learn the pros and cons of the new text splitting functions in Excel.

0:00 TEXTSPLIT
2:54 TEXTBEFORE & TEXTAFTER
4:28 Multiple Delimiters
5:44 Start with the First Name
5:51 Take care of the Last Name
6:35 Identify the Middle Name(s)
8:05 Splitting Other Types of Text
9:06 Splitting into Rows Instead of Columns
9:35 Splitting into Rows AND Columns

Related Videos:

14 NEW Excel Functions - TEXTSPLIT, Array Shaping, and more!:

How to Split Text in Cells with Flash Fill in Excel:

#MsExcel #ExcelCampus

[KeyserTheRedBeard]

astonishing video Excel Campus - Jon. I crushed that thumbs up on your video. Always keep up the superior work.

[jamesdewer]

You know your videos are good when a person is watching one at Sunday dinner. Thanks J

[johnborg]

Thanks Jon. Your explanation helps alot!!! :) :)

[shiffamohammed]

Thank you so much for the informative video on the new excel functions. I hope Microsoft will enhance the inconsistent behavior of textsplit function (working in a table range and not in excel table).

[Excelambda]

Great video!!✌
Alternative formulas for "Split 3 Names Tables" using TEXTSPLIT:

First: =TAKE(TEXTSPLIT([@[Full Name]], " "), , 1)
Last: =TAKE(TEXTSPLIT([@[Full Name]], " "), , -1)

alternative formula for "middle" : =IFERROR(TEXTJOIN(" ", , DROP(DROP(TEXTSPLIT([@[Full Name]], " "), , 1), , -1)), "")

If we have an array instead of a table, single cell formula:

=LET(a, A4:A13, d, "

[wayneedmondson]

Great demo Jon! Nice to be made aware of the options and possibilities of these functions. Thanks for sharing. Thumbs up!!

[karimibrahim]

Sir, Thank you Very Much for sharing these informative videoes. May the Lord Bless you 💝

[conradhaynes]

Superb presentation. Perhaps using the Mid and Len functions for extracting the middle names using TextBefore and TextAfter could be another option.

[darrylmorgan]

Really Like The TEXTSPLIT FUNCTION.Really Looking Forward To Trying All The New Functions When Microsoft Make Them Available To 365...Thank You Jon :)

[iankr]

Many thanks, Jon. I came across the incompatibility between spill functions and tables with another function yesterday. I hope they can fix this!

[IvanCortinas_ES]

Excellent video Jon, as professional as ever. Hopefully we'll soon be able to do spilled arrays inside tables. Thank you!!!

[joshuamanampiu]

Didn't know about the tables and which functions are compatible. Thank you for the tips.

[md.saifulislam]

Hey bro! Your video means something different.

[richardhay]

I do have and use all three functions. No favorite. Tool choice depends on the problem at hand. They each have specialized applications. But maybe a fourth function TEXTBETWEEN!! It's important to recognize that all Dynamic Array functiond have the same incompatibility withe the calc engine in tables. So MS will need to provide major changes to tables or else develop an interface between Dynsmic Arrays and the table. Maybe not so easy since they need to keep the table calc engine working with older versions of Excel.

[esthersuh]

Thanks, and I want to ask that is it similar to FIND or SEATCH function? Thumbs up up

[josecarlosconejo]

Have you tried to use an array of delimiters in all three formulas and check if they work? I am curious to see…

[nadermounir]

This is an amazing video 📹 Jon. I really enjoyed the fact that u showed us the old methods also. Thanks again for your hard work.
By the way, I am trying to take your VBA course but it is closed now. When will it be available?

[samuelanane-kyei]

Nice video. However, I think it'll be great if you don't show us already written formulas but you write the formulas as we're watching. That way, it'll be easier to understand the logic behind the formulas.

[robbiepool]

How will you split the following: Johannes Petrus Jan van der Merwe. First, second and third name and then the surname.

[lauriewallace]

How about addresses? I would like the street names all together to be able to make walk lists...