Excel For Mac Split Cells

by

Split A Cell in Excel, also known as Text To Columns and sometimes called as delimit, is used to split the data of a cell. We can spit a cell with different parameters such as Space, Blank, Commas or any other criteria which breaks a cell into 2 or more cells.

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.

Use the Developer tab to create or delete a macro in Excel for Mac. 2: Click Visual Basic to open Visual Basic Editor. 3: In Visual Basic Editor, click Insert in menu Module. Then place above VBA code under Module. One module for one custom function. Then save as Workbook as macro enabled workbook.xlsm. For your information: Create custom. In the Split Cells dialog box, you need to do as follows: (1) If you want to split cells to rows, please select the Split to Rows option, otherwise, select the Split to Columns option. (2) In the Specify a separator section, select New line option (3) Finally click the OK button; 3. In the next popping up dialog box, select a blank cell for. Hi kindly follow herewith mentioned steps in order to split the cell in half. Go to Data Tab in the Excel menu bar. Select the text to column option. Select the Delimited option in case you need to half the data based on any special chara.

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:

  1. Select the cells you want to split the data.
  2. 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.

ForMac

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)))

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.

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))

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

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 formula below:

=IFERROR(MID(A5,FIND(' ',A5)+1,FIND(' ',A5,FIND(' ',A5)+1)-FIND(' ',A5)),'-')

Here, the text in cell A5 does not have a middle name, so the formula returns the “-” character. It’s obvious that if we use it for cell A2, it will give the middle name.

We have covered three simple methods to split cells in Excel. Now, you have a choice, so see which one you are more comfortable with and try that one out.

You can connect with us and ask our experts for your inquiries and get more Excel Support Service.

Reduce cost, accelerate tasks, and improve quality with Excel Automation Service.

Split a Cell | Text to Columns | Flash Fill | Formulas to Split Cells

To split a cell in Excel, add a new column, change the column widths and merge cells. To split the contents of a cell into multiple cells, use the Text to Columns wizard, flash fill or formulas.

Split a Cell

Use the following trick to 'split' a cell in Excel.

1. For example, task B starts at 13:00 and requires 2 hours to complete.

Suppose task B starts at 13:30. We would like to split cell B3 and color the right half.

2. Select column C.

3. Right click, and then click Insert.

Result:

4. The default width of a column is 64 pixels. Change the width of column B and C to 32 pixels.

5. Select cell B1 and cell C1.

6. On the Home tab, in the Alignment group, click the down arrow next to Merge & Center and click Merge Cells.

Excel For Mac Split Cells Without

Result:

7. Repeat steps 5-6 for cell B2 and cell C2 (and cell B4 and cell C4).

8. Change the background color of cell B3 to No Fill.

Result:

Note: suppose task A ends at 15:30. Use the trick explained above to 'split' cell E2. Download the Excel file and give it a try.

Text to Columns

To split the contents of a cell into multiple cells, use the Text to Columns wizard. For example, let's split full names into last and first names.

1. Select the range with full names.

2. On the Data tab, in the Data Tools group, click Text to Columns.

The following dialog box appears.

3. Choose Delimited and click Next.

4. Clear all the check boxes under Delimiters except for the Comma and Space check box.

5. Click Finish.

Result:

Note: this example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.

Flash Fill

Do you like Magic? Instead of using the Text to Columns wizard, use flash fill to quickly split data into multiple columns.

1. First, split the contents of one cell into multiple cells.

2. Select cell B1 and press CTRL + E (flash fill shortcut).

Excel For Mac Split Cells Using

3. Select cell C1 and press CTRL + E.

4. Select cell D1 and press CTRL + E.

Note: flash fill in Excel only works when it recognizes a pattern. Download the Excel file and give it a try. Visit our page about Flash Fill to learn more about this great Excel tool.

How

How Do You Split A Cell In Excel On A Mac

Formulas to Split Cells

Split Excel Cell In Half

One drawback when using these tools is that the output will not automatically update when the source data changes. Create formulas to overcome this limitation. Let's split full names into first and last names.

Excel For Mac Split Cells Free

1. The formula below returns the first name.

Excel For Mac Split Cells Pdf

2. The formula below returns the last name.

How Do You Split Cells In Excel

3. Select the range B2:C2 and drag it down.

Note: visit our page about separating strings to understand the logic behind these formulas.