Nuraida Kashmin
Excel formula helps us to split a cell or a text string or a column. 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 cells or strings.
Practice Workbook
Download the following workbook and exercise.
8 Easy Ways to Apply a Formula to Split in Excel
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. These are Microsoft Excel Text Functions. Let’s say we have a dataset (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.
- Now type the formula:
=LEFT(B5,SEARCH(" ",B5)-1)
- Then 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 with 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.
- Next select Cell D5.
- Type the formula:
=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)
This will search for the space and return with the position of the space with the SEARCH function.
➤ 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.
Read More: How to Split Cells in Excel (The Ultimate Guide)
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 (B4:B14). We are going to use the INDEX-ROW formula to split this column into two columns (Column1 & Column2).
STEPS:
- First select Cell D5.
- Next, write down the formula:
=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.
- Select Cell E5.
- Type the formula:
=INDEX($B$5:$B$14,ROWS(E$5:E5)*2)
- Then 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: VBA to Split String into Multiple Columns in Excel (2 Ways)
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 help us to split one text string into multiple columns. Here we have a dataset (B4:E9) of sold items. We are going to split the sold item into three columns (CODE, SERIES, NUMBER).
STEPS:
- Select Cell C5.
- Next type the formula:
=LEFT(B5,3)
- Press Enter and use the Fill Handle tool to the below cells.
- Now select Cell D5.
- Type the formula:
=MID(B5,4,1)
- Hit Enter and use the Fill Handle to see the result.
- Again select Cell E5.
- Write down the formula:
=RIGHT(B5,3)
- Finally, hit Enter and use the Fill Handle tool to see the result.
Read More: Excel VBA: Split String by Number of Characters (2 Easy Methods)
4. Excel IF Formula to Split
To run a logical test in a given range, we use Excel IF function. It returns the value whether it is TRUE or FALSE. Let’s say we have a dataset (B4:F8) of customer payment history. 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")
- Now 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’.
- Then select Cell F5.
- After that, type the formula:
=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 One Cell into Two in Excel (5 Useful Methods)
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 with 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 (B4:C9) containing different writers’ names.
STEPS:
- First, select Cell D5.
- Next type the formula:
=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 search for the space and return with 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 space.
➤ 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 Excel 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:
- Select Cell C5 at first.
- Next write down the formula:
=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.
Read More: Excel Formula to Split String by Comma (5 Examples)
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 products name 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:
- Select Cell C5.
- Now type the formula:
=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 which is ‘5’.
➤ LEFT(B5, SEARCH(CHAR(10),B5,1)-1)
This will return the leftmost value.
Read More: Excel VBA: Split String by Character (6 Useful Examples)
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 Excel 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:
- First, select Cell C5.
- Next, write down the formula:
=TRANSPOSE(FILTERXML("<s>"&SUBSTITUTE(B5,",","</s><s>")& "</s>","//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.
Read More: How to split a single cell in half in Excel (diagonally & horizontally)
Conclusion
These are the quickest way 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.
