Using Excel TEXTSPLIT Function To Split Text in Columns

Published: May 31, 2024 - 5 min read

Hannah Recker
excel textsplit

The Excel TEXTSPLIT function transforms the way you maneuver and handle text within spreadsheets by effectively splitting text across cells. Before TEXTSPLIT, users depended on more complex formulas or manual procedures for splitting text across cells.

TEXTSPLIT refines these tasks by offering a direct approach to split text based on an explicit basis straightly within Excel.

Dive a bit deeper into how this superb feature actually splits your texts into tidy columns.

Understand The Working Of TEXTSPLIT Function Syntax

Explanation Of How TEXTSPLIT Splits Text Strings By A Delimiter Into A Dynamic Array

The Excel TEXTSPLIT function is created to divide a text string into individual components based on explicit delimiters, efficiently dispensing the ensuing characters into an Excel range as a dynamic arrangement. This function is specifically beneficial when handling data that needs to be divided for further inspection or refining.

For instance, if you have a string like “Aiden, Markram,32” and you use a comma ( , ) as your column delimiter, Excel TEXTSPLIT will divide each component into a new cell in the same row.

put textsplit into action

Got it? Good. Next up, we’re slicing up those cell contents like a ninja—let’s talk about putting TEXTSPLIT into action.

Explanation Of The TEXTSPLIT Function Syntax And Parameters

The syntax for the Excel TEXTSPLIT function is as follows:-

  • Text: A text is a string to the cell encompassing the string that you want to divide.
  • col_delimeter: This framework depicts the delimiter utilized to split the text into piers. It can be a character or an arrangement of characters.
  • [row_delimeter]: A voluntary variable that prescribes the delimiter to split the text into rows. If erased, the function will only divide the text into columns.
  • [ignore_empty]: The voluntary boolean framework decides whether to avoid empty text outcomes between successive delimiters.
  • [match_mode]: This is an optional variable that prescribes how delimiters are matched in the text, such as exact matches or partial matches.
  • [pad_with]: This voluntary structure depicts a value to fill in cells if the rows made by the row delimiters do not all include the same number of columns.

How To Use TEXTSPLIT To Split A Cell In Excel

If you want to split text horizontally in Excel, just enter =TEXTSPLIT(A1, “,”) in a cell adjoining your text cell, substituting “,” with your selected delimiter.

textsplit structure

Entering The TEXTSPLIT Formula

Once you have entered the text in the string, it’s time to enter the TEXTSPLIT formula and make sure that you type the formula in the correct cell source and delimiters are used to meet your particular data requirements.

textsplit substrings

Using Substrings As Delimiters

TEXT SPLIT permits the use of substrings as delimiters, permitting the dividing of text based on more intricate patterns.

Handling Multiple Delimiters

By utilizing the arrangement stable in the delimiter row, TEXTSPLIT can handle multiple delimiters contemporaneously, like =TEXTSPLIT(A7, “,”).

textsplit with multiple delimiters

Dividing Text Across Both Axes

If you want to split text both horizontally and vertically, integrate both the piers and row delimiters in your function of Excel TEXTSPLIT, such as, =TEXTSPLIT(A2, “,”).

using textsplit with substitute

Refining Split Results

Merge TEXTSPLIT with SUBSTITUTE to purify undesirable characters from your outcome. Adapting the delimiter arrangement helps handle prefixes like “Mr.” or “Ms.” effectively.

Examples Of TEXTSPLIT In Excel

Splitting Names

Smoothly split names divided by commas into particular cells to arrange and identify data more efficiently.

excel textsplit concurrence with text function

Parsing Dates

Use Excel TEXTSPLIT in concurrence with the TEXT function to analyze dates arranged in distinct styles.

analyze dates arranged in distinct styles in excel

Separating First And Last Names

Effectively divide full names into split columns for first and last names, adapting for distinct row delimiters as required.

divide full names into split columns first and last

Feeling a bit old school or don’t have Excel 365? No worries, I’ve got some tricks up my sleeve for you too.

Alternatives To TEXTSPLIT In Older Excel Versions

If you are using earlier versions of Excel that do not have the Excel TEXTSPLIT function, you can still manipulate text using conventional functions like SEARCH, FIND, RIGHT, LEFT, and MID, although these methods might be more complex. These techniques indulge more steps but can accomplish similar outcomes.

Below given are various alternatives and techniques you can use:-

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 314,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Alternative 1: Using TEXT TO COLUMNS

If you want to follow a non-formula approach, Excel’s Text to Columns feature is useful:-

Step 1: Choose the cells that you want to split. Then, go to the Data tab and click on Text to Columns.

text to columns in excel

Step 2: Selected Delimited > Next.

convert text to columns wizard in excel

Step 3: Choose the Delimiters for your data like commas and spaces.

choose delimiters for data in excel

Step 4: Click on Finish.

complete text to columns update in excel

Alternative 2: Using SUBSTITUTE and RIGHT

For more intricate scenarios, like dividing and getting the nth component from the end, you can merge SUBSTITUTE, RIGHT, and MID.

These two functions will help you to split text in the older versions of Excel that don’t contain the Excel TEXTSPLIT function.

Congrats on making it this far! Let’s wrap things up and talk about why TEXTSPLIT might just be your new best friend in Excel.

Using The TEXTSPLIT Function In Excel

Excel TEXTSPLIT stands out as a useful and powerful function in Excel 365, substantially improving text dissecting abilities by refining data handling tasks, from simple splits to intricate dissecting scenarios, ensuring that users can manage text data more effectively than ever before.

Whether you are handling large datasets of names, addresses, or any other details that need subdivision, Excel TEXTSPLIT adjusts to your requirements with accuracy by saving time and managing your datasets in an organized manner.

Get in touch with Coefficient and grab this breathtaking tool in your Excel toolkit, for identifying text data instinctively and effectively than before.

Sync Live Data into Excel

Connect Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies