Though the condition of splitting a cell can be changed or customized by space between the words or by character between the words such as a comma depending upon the data. Click here - to use the wp menu builder; Sign in. Log into your account. Forgot your password? Password recovery. Select the cell range you want to clear. Select by dragging the mouse or entering the name of the cell range into the Name box. Choose Edit→Clear from the menu bar. Alternatively, click the Ribbon’s Home tab, and in the Edit group, click Clear. Splitting cells in Excel spreadsheet I have Excel 2011 and I have helped create a database for an organization. Each row belongs to a company and in the row there is a column for for names with generally three entries (all in one cell) The same applies to a column for emails (3 within one cell) Is there an efficient way to split the info in the.

Next, select the row below and to the right of where you want to split. Select row 11 cell A11 to split row 10. Next, click the Layout tab of the ribbon; Select the Window group and choose Split button. Excel gives you two areas that are scrollable and which contain the whole worksheet.

When you copying data from an external source, more often than not, you’ll get the entire data stuffed into a single column. This is rarely how you want to see data in Excel, as the whole point behind using spreadsheets is to be able to manipulate and analyze data without fuss. Let us show you how to split cells in Excel and manage your data easily using the Text to Columns feature.

Text to Columns feature dialog presents various options about how you want to slice your data. You can define data types, in addition to how you want to slice the columns.

1. Begin by selecting your data.
2. Click the Text to Columns icon under the DATA tab in Excel’s top menu.
3. Continue using either one of these two methods of slicing:
   1. Select the Delimited option if your data contains certain characters for separating columns (i.e. commas).
   2. Select the Fixed width option if each field has a certain character length.
4. Click the Next button to proceed.
5. Choose where the fields are to be separated.
   1. If you chose the Delimited option, select which characters are used as separators.
   2. If you chose the Fixed width option, determine the column borders by clicking on the preview window.
6. Click the Next button when you’re done.
7. You can format the columns and select a Destination cell to specify where columns will begin.
8. Click the Finish button to split the cells.

Excel will keep records of your actions and you can use Text to Columns feature rapidly when you update the data.

Split text string at specific character in a cell in Excel

This tutorial explains how to split texts string at a specific character into separate column cells with formulas in Excel.

How to split text string at specific character in a cell in Excel?

Supposing you have a text string list in column B as the below screenshot, and you want to split texts at the character “-” and output them in three separate columns. In this case, the below formulas can do you a favor.

Generic formulas

Generic formula for splitting the left text

=LEFT(text_string,FIND('Char', text_string)-1)

Generic formula for splitting the middle text

=MID(text_string, FIND('Char', text_string) + 1, FIND('Char', text_string,FIND('Char', text_string)+1) - FIND('Char', text_string) - 1)

Generic formula for splitting the right text

=RIGHT(text_string,LEN(text_string) - FIND('Char', text_string, FIND('Char', text_string) + 1))

Arguments of above formulas

Text_string: The text string that you want to split text from;

Char: The character you will split text based on.

How to use these formulas?

Firstly, split the first text string based on specific character

1. Select a blank cell to output the result. Here I select C3.

How Do You Split Cells In Excel 2011 For Mac Os

2. Enter the below formula into it and press the Enter key. And then drag the cell with your LEFT formula all the way down to apply it to other cells.

=LEFT(B3,FIND('-',B3)-1)

Secondly, split the middle text string based on specific character

1. Select a blank cell such as D3 to output the result.

2. Enter the below formula into it and press the Enter key. Select the result cell, drag the Fill Handle down to apply the formula to other cells.

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

Finally, split the last text string based on specific character

1. Select a blank cell such as E3 to output the result.

2. Enter the below formula into it and press the Enter key. Select the result cell, drag the Fill Handle down to apply the formula to other cells.

=RIGHT(B3,LEN(B3) - FIND('-', B3, FIND('-', B3) + 1))

Now text string in each cell of column B have been split into three parts.

Tips: If you only want to split the text string into two parts based on a specific character, after splitting the first part with the above LEFT formula, you can apply the below RIGHT formula to extract the second part as the below screenshot shown.

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

How these formulas work?

=LEFT(B3,FIND('-',B3)-1)

• FIND('-',B3): The FIND function returns the starting position of character “-” in the text string KTE-Jan-359. The result is 4.
• LEFT(B3,4-1): The LEFT function extracts 3 characters (4-1=3) from the left side of the text string KTE-Jan-359. Here the result is KTE.
  Note: As the first character “-” is in the 4th position of the text string, you need to subtract 1 to exclude it from extracting.

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

• FIND('-',B3) + 1: The FIND function returns the starting position of character “-” in the text string KTE-Jan-359. Here the result is 4.
  Note: As the first character '-' is in the 4th position of the text string, you need to add 1 to 4 in order to extract texts after it, and finally we end of with 4+1=5.
• FIND('-',B3,FIND('-',B3)+1) here is shown as FIND('-',KTE-Jan-359,4+1): The Find function finds the position of character '-' starting from the 5th character in KTE-Jan-359. And returns the result as 8.
• MID(B3, 5, 8 - 4 - 1): MID(KTE-Jan-359,5,3) which means that the MID function extracts 3 number of characters starting from the 5th character in the text string KTE-Jan-359. And the result is Jan.

=RIGHT(B3,LEN(B3) - FIND('-', B3, FIND('-', B3) + 1))

• LEN(B3): The LEN function calculates the total length of text string KTE-Jan-359, and returns the result as 11.
• FIND('-',B3,FIND('-',B3)+1): The Find function finds the position of character '-' starting from the 5th character in KTE-Jan-359. And returns the result as 8.
• RIGHT(B3,11 - 8)): The RIGHT function extracts 3 (11-8) characters from the right side of KTE-Jan-359. Here the result is 359.

Related functions

Excel LEFT function
The LEFT function extracts the given number of characters from the left side of a supplied string.

Excel FIND function
The FIND function is used to find a string within another string, and returns the starting position of the string inside another one.

How Do You Split Cells In Excel 2011 For Macs

Excel MID function
The MID function is used to find and return a specific number of characters from the middle of given text string.

Excel RIGHT function
The RIGHT function extracts a specific number of characters from the right side of the text string.

Excel LEN function
The Excel LEN function returns the number of characters in a text string.

Related formulas

Split dimensions into two parts in Excel
This article explains how to use formulas to split dimensions in a cell into two parts without units (individual length and width).

Separate numbers from units of measurement
This article explains how to use formula to separate numbers from unites of measurement.

How Do You Split Cells In Excel Mac 2011

Split text and numbers in a cell in Excel
If you want to split text and numbers in a cell into different column cells with formulas, this tutorial will do you a favor.

Split Text With Delimiter In A Cell In Excel
In this article, we are providing a step-by-step guide to help you split text in a cell with specific delimiter in Excel.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

• Designed for 1500 work scenarios, helps you solve 80% Excel problems.
• Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
• Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
• 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.