We can split a cell, a text string, or a column using an Excel formula. This makes the dataset more readable and easily accessible to the correct information. In this article, we are going to see how an Excel Formula is used to split data or strings.
How to Split Data with Formula in Excel: 8 Easy Ways
1. Excel Formula with LEFT & RIGHT Functions to Split Cell
The LEFT function returns the leftmost characters and the RIGHT function helps us to extract the last characters from a text string. Let’s say we have a dataset of range B4:D9 with some random names. We are going to use a formula to split the cells containing those names.
STEPS:
- Select cell C5 at first.
- Then, type the formula.
=LEFT(B5,SEARCH(" ",B5)-1)
- Press Enter and use the Fill Handle to see the results in the next cells.
Formula Breakdown
➤ SEARCH(” “,B5)
This will search for the space and return the position of the space with the SEARCH function.
➤ LEFT(B5,SEARCH(” “,B5)-1)
This will extract all the characters on the left and return the value.
- Select cell D5 and type the formula below.
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))
- In the end, hit Enter and use the Fill Handle tool to see the result.
Formula Breakdown
➤ SEARCH(” “,B5)
The SEARCH function returns the position of the space.
➤ LEN(B5)
This will return the total number of characters with the LEN function.
➤ RIGHT(B5,LEN(B5)-SEARCH(” “,B5))
This will return the last name value.
2. INDEX-ROWS Formula to Split One Column into Multiple Columns in Excel
Excel ROWS function is used to return the row number and the INDEX function returns the value from the given range. We can use the combination of these two functions to split one column into multiple columns. Assuming we have a dataset of range B4:B14. We are going to use the INDEX-ROWS formula to split this column into two columns (Column1 & Column2).
STEPS:
- Write down the formula on cell D5.
=INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)
- Now press Enter and use the Fill Handle tool to see the result.
Formula Breakdown
➤ ROWS(D$5:D5)*2-1
This will return the row number.
➤ INDEX($B$5:$B$14,ROWS(D$5:D5)*2-1)
This will return the value from the range $B$5:$B$14.
- Type the formula on cell E5.
=INDEX($B$5:$B$14,ROWS(E$5:E5)*2)
- Hit Enter and use the Fill Handle to autofill the below cells.
Formula Breakdown
➤ ROWS(E$5:E5)*2
This will return the row number.
➤ INDEX($B$5:$B$14,ROWS(E$5:E5)*2)
This will return the value from the range $B$5:$B$14.
Read More: How to Separate Text and Numbers in Excel
3. Excel Formula with Combination of LEFT, MID & RIGHT Functions to Split a Text String
Sometimes we need to split a text string. Microsoft Excel LEFT function returns the leftmost characters of a text string and the RIGHT function helps us to extract the last characters from a text string. On the other hand, the MID function pulls out the middle characters from the middle of a text string. The combination of Excel LEFT, MID & RIGHT functions helps us to split one text string into multiple columns. Here we have a dataset of sold items in range B4:E9. We are going to split the sold item into three columns (CODE, SERIES, NUMBER).
STEPS:
- Move to cell C5.
- Next type the formula below.
=LEFT(B5,3)
- Press Enter and use the Fill Handle tool to the below cells.
- Insert the formula on cell D5.
=MID(B5,4,1)
- Hit Enter and use the Fill Handle to see the result.
- Again select cell E5 and input the fomrula.
=RIGHT(B5,3)
- Finally, hit Enter and use the Fill Handle tool to see the result.
4. Excel IF Formula to Split
To run a logical test in a given range, we will use the IF function. It returns the value whether it is TRUE or FALSE. Let’s say we have a dataset of customer payment history in the range B4:F8. We are going to split up the column named AMOUNT into two columns (CASH & CARD).
- In the beginning, select Cell E5.
- Next type the formula.
=IF(C5="Cash",D5,"N/A")
- Press Enter and use the Fill Handle tool to see the result.
This formula will return the AMOUNT value which is paid in Cash in cell E5. Otherwise, it will return N/A.
- Now, select cell F5 and type the formula below.
=IF(C5="Card",D5,"N/A")
- At last, press Enter and use the Fill Handle tool to the below cells.
This formula will return the AMOUNT value which is paid in Card in cell F5. Otherwise, it will return N/A.
Read More: How to Split Data in Excel
5. Combination of IFERROR, MID, SEARCH Functions to Split Middle Word
To avoid any error in the formula, we use the IFERROR function as it returns another possible result. Sometimes we have a dataset where each cell contains three words. We can use the MID function to extract the middle word. But if there is no middle word, it will show an error. For that, we use the IFERROR function along with the MID & SEARCH functions to split the middle word in Excel. Suppose we have a dataset containing different writers’ names.
STEPS:
- First, select cell D5 and insert the formula below.
=IFERROR(MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)),"")
- In the end, press Enter and use the Fill Handle tool to the below cells.
Formula Breakdown
➤ SEARCH(” “,B5)
This will return to the position of the space with the SEARCH function.
➤ MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5))
This will return the middle word by using the position difference between the first and second spaces.
➤ IFERROR(MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)),””)
This will return a blank space if there is no middle word in the cell.
6. Excel Formula with SUBSTITUTE Function to Split Date
To replace a specific character in a given range with another, we use the SUBSTITUTE function. We can use an Excel formula with SUBSTITUTE, LEN & FIND functions wrapped in the RIGHT function to split the date from the cell. We have to remember that the formula can be used only when there is a date at the end of the cell like the below dataset (B4:C8).
STEPS:
- Write down the formula on cell C5.
=RIGHT(B5,LEN(B5)-FIND("~",SUBSTITUTE(B5," ","~",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2)))
- Finally, hit Enter and use the Fill Handle tool to autofill the cells.
Formula Breakdown
➤ LEN(B5)
This will return the length of the text string.
➤ SUBSTITUTE(B5,” “,””)
This will replace all the spaces in cell B5.
➤ LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))
This will subtract the length without space from the total length.
➤ SUBSTITUTE(B5,” “,”~”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))-2)
This will place ‘~’ character between the name and the date.
➤ FIND(“~”,SUBSTITUTE(B5,” “,”~”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))-2))
This will find the position of ‘~’ character which is 4.
➤ RIGHT(B5,LEN(B5)-FIND(“~”,SUBSTITUTE(B5,” “,”~”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))-2)))
This will extract the date from the text string.
7. Excel Formula to Split Text Using CHAR Function
Excel CHAR function is a Text function. It means character. It returns a character that is specified by the ASCII code number. We can use the CHAR function to split text by line break as this function supplies the break character. Assuming we have a dataset (B4:C8) of Microsoft product names with year. We are going to extract the product name using the CHAR & SEARCH functions wrapped into the LEFT function. Here the ASCII code for the line is 10.
STEPS:
- Type the formula on cell C5.
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)
- Then hit Enter and use Fill Handle to see the result.
Formula Breakdown
➤ SEARCH(CHAR(10),B5,1)-1
This will search for the position of the text string denoted by CHAR(10).
➤ LEFT(B5, SEARCH(CHAR(10),B5,1)-1)
This will return the leftmost value.
Read More: Split Names in Excel
8. FILTERXML Formula to Split in Excel
To see the output text as a dynamic array after splitting, we can use the Excel FILTERXML function. It is available in Microsoft Office 365. Let’s say we have a dataset (B4:B8) of customers’ payment history. We are going to split the customer names and the payment methods.
STEPS:
- Select Cell C5.
- Next, write down the formula.
=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")& "</s></t>","//s"))
Here the sub-node is represented as ‘s’ and the main-node is represented as ‘t’.
- Then press Enter and use Fill Handle to autofill the below cells.
Formula Breakdown
➤ FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”)
This will turn the text strings into XML strings by changing the delimiter characters into XML tags.
➤ TRANSPOSE(FILTERXML(“<t><s>”&SUBSTITUTE(B5,”,”,”</s><s>”)& “</s></t>”,”//s”))
The TRANSPOSE function will return the output horizontally instead of vertically.
Practice Workbook
Download the following workbook and exercise.
Conclusion
These are the quickest ways to use Excel Formula to split. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
