Table of Contents
The TEXTSPLIT function in Excels can be used to split text based on a specific delimiter.
To get the first item that results from this function, you can use the following syntax:
=CHOOSECOLS(TEXTSPLIT(A2, " "), 1)
This particular example will split the text in cell A2 using a space as a delimiter and then it will return only the first item that results from the split.
For example, if the string in cell A2 is Andy Bernard then this formula will return Andy.
The following example shows how to use this formula in practice.
Example: How to Split Text and Get First Item in Excel
Suppose we have the following column of names in Excel:
Suppose we would like to split the names based on where a space occurs and then get only the first item that results from the split.
We can type the following formula into cell B2 to do so:
=CHOOSECOLS(TEXTSPLIT(A2, " "), 1)
We can then click and drag this formula down to each remaining cell in column B:
The formula splits the names in column A based on where the space occurs and then only returns the first item from the split.
For this particular example, the first name of each person in column A is returned.
Note that if you’d like to return the second item instead, you can replace 1 with 2 in the formula:
=CHOOSECOLS(TEXTSPLIT(A2, " "), 2)
The following screenshot shows how to use this formula in practice:
This time the formula returns the second item from the split.
How This Formula Works
Recall the formula that we used to split the text and get the first item from the resulting split:
=CHOOSECOLS(TEXTSPLIT(A2, " "), 1)
Here is how this formula works:
First, the TEXTSPLIT function splits the text in cell A2 based on where the space occurs.
For example, the name Andy Bernard is split into one column that contains Andy and a second column that contains Bernard.
Next, the CHOOSECOLS function selects only the first column from the output.
The end result is that we’re able to get only the first item from the split, which is Andy.