The steps to split a cell with Text to Columns are: Select the cells containing the text to be split. From the ribbon, click Data Text to Columns. The Convert Text to Columns Wizard dialog box will open. Select the Delimited option. This allows us to split the text at each occurrence of specific characters. Below are the steps that will split multiple lines in a single cell into separate cells: Select the entire dataset that you want to split. Go to the Data tab. In the Data Tools group, click on the Text to Columns option. In the Text to Columns dialog box, in Step 1 of 3, select Delimited and click ‘Next’. In Step 2 of 3, uncheck any. You can’t split a cell. If a cell has never been merged with another cell, it can’t be unmerged. Note: You can’t use forms for tables with merged cells. Forms in a Numbers for iPhone or iPad spreadsheet are hidden when you open the spreadsheet in Numbers for Mac.

Comfortable and satisfying as it is, working with Excel can sometimes be confusing. There sure have been times when you entered a lot of data in single cells under a column and decided later that all the information in those single cells better be divided into different cells under multiple columns. At times like this, you need to know how to split cells in Excel!

In this tutorial, we are going to explain three methods to split cells in Excel.

Splitting Cells Using Text to Column Feature

Suppose that you have a spreadsheet containing some information about a group of people, including first name, last name, and ID number and you want to put this information in separate cells.

You can split these data following these steps:

Select the cells you want to split the data.
Go to the Data tab and choose “Text to Column” from the Data Tools group.

3. The “Convert text to column wizard” window will open. This part includes three steps:

Step 1 of 3: At this step, you have 2 options: Delimited and Fixed Width. Choose the “Delimited” option, which is the character by which you specify to split cells and then click Next.

Step 2 of 3: Since our data are separated by space and comma, you must choose both as delimiters and then click Next. Also, you’d better check the “Treat consecutive delimiters as one” box. You can see the result in the Data preview section.

Step 3of 3: At this stage, you can specify the data format and the data destination. Leave the data format as General. Change the destination to $B$2 by typing it or clicking on the icon and selecting a range on the spreadsheet. Having set the information, click Finish.

And here’s the result.

Note: If you leave the destination as default, Excel will keep the first column where it is and move the rest to the next columns.

Splitting Cells Using the Flash Fill Feature

Another easy way to split cells in Excel is using the flash fill option. To split cells in the previous example, we just need to write the part of the text that we want splitted in the desired cell, then use the flash fill feature.

Suppose that you want to extract the first names, last names, and ID numbers from the first column and put them in the next columns. All you need to do is to follow these steps:

In Cell B2, type the corresponding first name (i.e., Matilda).
Press “Ctrl+E,” which is the shortcut for the flash fill option.
Do the same thing for the last names and ID numbers.

Note: If you don’t want to use shortcuts, you can go to the Data tab and click on Flash Fill from the Data Tools group.

Splitting Cells Using Text Functions

The last method to split cells is using Excel text functions. Excel text functions work great when you want to split cells. You must know how to use these functions and how to combine them.

Here are the text functions that we can use to split cells in Excel:

LEFT: It extracts a specific number of characters from the left side of a string
FIND: We use it to find a string inside another one. It returns the starting position of the sub-string as a number.
RIGHT: It extracts a specific number of characters from the right side of a string.
LEN: It returns the total number of characters in a text string.
MID: It extracts a substring from a text string. It returns the number of characters starting from the position you specify.
SUBSTITUTE: It replaces one or more text strings with another one.

Note: Excel SEARCH function does the same thing as the FIND function. The only difference is that the FIND function is case-sensitive, but the SEARCH function is not.

Now that we know the text functions we need, we can directly go to the examples.

Extracting the First Name

This example shows how to extract the first names from a list containing first names and last names.

To extract the first names, we can use the combination of the LEFT function and the FIND function. To do so, we use the following formula:

=LEFT(A2,FIND(' ',A2))

In this formula, the FIND function finds the space character’s location as a number and gives it to the LEFT function to split the first name from cell A2.

Note: This formula works for the previous example too.

Extracting the Last Name

To extract the last names, we use a combination of the RIGHT, FIND, and LEN functions. To do so, we use the formula below:

=RIGHT(A2,LEN(A2)-FIND(' ',A2))

This formula locates the space character and splits the characters after it.

If our list includes middle names, we have to combine the above functions with IF and SUBSTITUTE functions. Look at the formula below:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,' ','))=1,RIGHT(A2,LEN(A2)-FIND(' ',A2)),RIGHT(A2,LEN(A2)-FIND(' ',A2,FIND(' ',A2)+1)))

Excel Mac Split Cell Diagonally

In this formula, the IF function checks the existence of a middle name. The formula counts the number of the space characters; if there is only one, it uses the exact formula we introduced for extracting the last name before; if there are two spaces, it locates the second one and splits the next characters. Tp-link modems driver download.

Now, what if we had the first example’s data and wanted to extract the last names? The answer is the combination of the MID and FIND functions as below:

=MID(A2,FIND(' ',A2)+1,FIND(',',A2)-FIND(' ',A2)-1)

Extracting the Middle Name

Sometimes, you have a list of names that include middle names, like the second example about the last names. To extract the middle name from a text, we combine the MID and SEARCH functions. Look at the following formula:

=MID(A2,SEARCH(' ',A2)+1,SEARCH(' ',A2,SEARCH(' ',A2)+1)-SEARCH(' ',A2)) Smart usb devices driver download for windows.

In this formula, the MID function extracts the characters between the two spaces.

How Do You Split A Cell In Excel On A Mac

Note: If you enter the above formula for a text without a middle name, it returns the #VALUE error. To avoid this, you can use the IFERROR function. Look at the

Excel For Mac Split Cells