Need to know how to split and freeze columns in Excel 2011? Whether you're new to Microsoft's popular digital spreadsheet application, new to Excel 2011 or even an expert merely looking to brush up on the fundamentals, you're sure to be well served by this quality video tutorial from the folks at Lynda. For all of the details, and to get started splitting and freezing rows and columns in your. Answer: Select the cells that you wish to merge. Right-click and then select 'Format Cells' from the popup menu. When the Format Cells window appears, select the Alignment tab. Check the 'Merge cells' checkbox. Click on the OK button. Now when you return to the spreadsheet, you will find your selected cells merged into a single cell. Selecting the text to columns button will prompt you with a wizard to walk you through splitting your cells. In step one, you will choose whether you want your text separated at a fixed width or delimited. A fixed width gives you complete control over your cell split. If you want to split it after 3 characters, you can, or 7 characters.

On the Data tab, in the Data Tools group, click Text to Columns. The Convert Text to Columns Wizard opens. Choose Delimited if it is not already selected, and then click Next. Select the delimiter or delimiters to define the places where you want to split the cell content.

If you want to extract the information from one cell into two or multiple cells splitting the cell is required. Splitting cells also provide better sorting and filtering of an existing table. In excel, there are many ways to split cells. Some of the processes are Unmerge cells, Flash Fill, and Text to Column feature. You can also use formulas or VBA code to split cells. In this article, we will see the different processes on how to split cells in excel.

Table of Contents

Splitting Cells by Using the Unmerge Cells Option in Excel

While working on an imported excel file, you may find that some of the cells of your imported spreadsheet are merged together. You may not require the merged cells. There is an easy way to unmerged/split these cells. All you can do is select the merged cells together and click on the Unmerge Cells option on top.

How to Split a Single Cell Diagonally in Excel

For splitting a single cell diagonally, you need to get the help of the format cells option. Suppose you have a single cell where two words are written. The words are “Up” and “Down”. You wish to split that two words diagonally in a single cell. To begin the operation, 1^st select that targeted cell and click on the right button of your mouse to select the Format Cells option. Under the Format Cells dialogue box, click on to the Border option and then select the diagonal border and press OK.

You will see a diagonal line has appeared. Now for adjusting the positions of the words, select the first word “Up” and click on the drop-down option of Font under the Home tab. A new dialogue box of Format Cells option will come up which will only highlight the fonts. Select the Superscript option under Effect and choose the font size and style you want and then press OK.

Following the same procedure select the second word and under the Effect, bar click on the Subscript option and then press OK.

Now for aligning the words perfectly put some extra space between your words. By this, you will get to see the words aligned diagonally in a single cell. This is how you can split a single cell diagonally.

Let’s do the same thing briefly by inserting a box for a better view. Before doing the same steps above, make the height and width of the targeted cell larger by dragging it and set the alignment of the 1^st word on top. Now insert a triangle-shaped object in that box by choosing the Shapes option under the Insert tab while pressing the Alt on the keyboard. It will lock down the object in your cell so that by dragging up or down the cell the alignment of that object is not disturbed. Rotate the object just the way you like and write whatever you want and align it to right. You will see that the two words are split diagonally. The word “Up” is in the upper side of the cell and the word “Down” is in the lower right corner of the cell inside the box.

How to Use Text to Columns Feature to Split Cells in Excel

The feature of Text to Columns in excel allows text strings to be split by some delimiter, which are the semicolon, comma, space or other characters. Suppose we have a data table where the participant column has information about different candidates. We want to split this column into three where the new columns will be the First & Last name, Age, and Country. The whole procedure is given below.

• Make three new columns titled as Last name, Age, and Country.

• Choose the cells you want to separate under the Data option on top and then click on to Text to Columns.

• A dialogue box of Convert Text to Columns wizard will come up. In the 1^st step click on to Delimited as the file type and press Next.

• After that, specify the Delimiters and text qualifier if needed. Here we choose Tab, comma, and space as Delimiters.

• In next step, you will select the range to split the data. Beside the Destination box press the arrow icon to define the range. Then press Finish.

You will find the exact result that you wanted. In 1st column, write Frist Name instead of Candidate information. Set the alignment by your choice for a good view.

Note: You can also fix the width of each column in step 1 of Convert Text to Columns wizard box. If your table contains any dates, you can also apply the date format of your choice in step 3.

How to Separate the Content of a Cell in Excel with Flash Fill

If you are using the latest version of Microsoft Excel, you can be benefitted with the Flash Fill option. The Flash Fill option in excel was introduced from 2013 version. I am using the Excel 2016 version. Using the Flash Fill option in 2016 version is like performing magic. To perform flash fill you need to create some extra columns beside your main column.

In your example, our main column consists of a candidate`s name, age, and country. We will split this column into 4 different columns to show the First & Last name, Age, and Country. It is mandatory to have the main column along with the other columns. After inserting data to the whole table, you can delete the main table. The procedure is stated below.

How To Split Cells In Excel

• 1^st create 4 new columns beside the main column.

• In the cell, B2 write Albert and press enter. After this go to the Data option on top and click on to the Flash Fill indicating cell B2. You will see that all the first name is inserted automatically in the adjacent column.

• Carry on this process for the other columns. Like for the Column Age, press 62 in column D2 and perform the Flash Fill.

Note: Don`t delete or edit the main column before performing the flash fill. You may find that same name is copied every time you perform the flash fill. In that case, click on the Refresh All under the Data option of excel and then repeat the operation again.

How to Split Cells in Excel Using Formula

In Excel, we can use different functions like LEFT, RIGHT, MID, LEN and SEARCH to split the content of a cell. Together these functions make formulas which can be used to extract content from a cell and it can also be used in other cells. In this example, I used some formulas to extract First & Last Name, Full Name and Age from a specific column and I used carried out the result in different columns. The procedure is given below.

• 1^st create 4 new columns beside your main column.
• In cell B2, write down the formula

  and press Enter. Drag down the formulated cell to copy the formula for the rest of the cell of column B (First Name). You will see that it will show the first name only just like the below picture.

• To carry out the last name we will follow the same procedure, only the formula will be changed. In cell C2, write down the formula,

  and drag down the formula to the rest of the cell of column C.

• Write the formula,

  in cell D2 and drag down the formulated cell to show full name.

• Finally, in cell E2 write the formula,

  pressing enter drag down the formulated cell to copy this formula for the rest of the cells of column E. It will show the age of the candidate from column A.

VBA Code to Split the Content of a Cell

There are many codes available to split cells in excel. In this example, we used a simple code which split the content of a cell into 3 cells. We will see the Name, Age and Country name which are split using this code. The code is given below which was found out from this link https://superuser.com/questions/971293/need-to-split-a-line-of-text-in-an-editable-cell-deliminated-by-signs/971314#971314

The following picture shows the result of the code.

Conclusion

In this article, we can see different methods of splitting cells in excel. Among all of them, using the Text to Column feature is the best option as you can eliminate different characters and you can also have a preview of the splitting. Flash Fill can also be used but some error may occur while performing this feature.

Hope this article may help you. Leave a comment below and let me know if you know any other approach of splitting cells in excel.

Related Readings

Splitting of texts is one of basic tasks in excel. We often get need of splitting names, ids, address etc. from a concatenated text. To do so we target a separator text in the string and use to split each segment of string. In this article, we will learn how to split text based on one or more characters in the string.

Split text string at specific character using formula

We can split texts at specific character using function LEFT, RIGHT, LEN and FIND. There will be of course at least two parts of text split. First, the text before specific text (text on left of the character) and second, the text after the specific text (text on the right of the character).

Split text on the left of string

Let's see how we can split text on the left of the characters.

Generic Formula

String: The string from which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Specific character: The character/s at which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Let’s see an example to make things clear.

Example: Split Text on the left of string based on a character

Here I have this simple data set. In range A2:A1, we names and codes concatenated with comma (','). The name is on left of comma and I want split it from each text.
Apply above generic formula here to get text on the left of the comma in string. Copy it in B2 and drag down.

You can see that each name is extracted from the string precisely.

How it works?

As we know that LEFT function extracts given number of characters from given string. Now let's break down the formula inside out.

FIND(',',A2): Here FIND function looks for position of search string comma (',') in cell A2 (Georgina Schalk,30). It returns 16. Now the formula is =LEFT(A2,16-1).

Since we don't want comma (',') to be part of the extracted string, we subtract 1 from 16. (if the target characters were 2 then we would subtract 2 and so on. In short, we subtract length of the search string from the position of the search string to not include it in the result).

Finally, we will have LEFT(A2,15) and we get name extracted (15 characters from left).

Split text on the right of string

We learned how to get text on left of a specific character. Now, let's see how we can split text on the right of the specific character/s.

Generic Formula

How Do You Split Cells In Excel 2011 For Mac Os

String: The string from which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Specific character: The character/s at which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Let’s see an example to make things clear.

Example: Split Text on the right of string based on a character

How Do You Split Cells In Excel Mac 2011

We will use the same data that we used in above example. This time we need to extract codes from the string. The codes are on right of the comma (','). Lets use above generic formula to split text from right.
In cell C2, write this formula and drag down to get each value.

How it works:

Let's break it down from inside. The LEN function returns the number of characters in the string.

LEN(A2) returns 18. The FIND function returns 16, as it does previously. Now the formula solves to RIGHT(A2,18-16), which again resolves to RIGHT(A2,2).

Now as we know that RIGHT function returns the given number of characters from right of a given string, hence we get 30 (2 characters from right).

So we have split text at specific text in a string using text excel function. It is dynamic. Whenever you will enter a new data in column, you will get extracted text in adjacent columns (if you copy formula in adjacent cell). Using this method you will have original text intact. If you want, you can value paste of output and delete the source data.
Split text at specific character/s using Text To Column Feature
Using above formula, we can only separate text on the first appearance of specific text in string. If there were multiple commas at random places in text, above formula would be useless. We will have to use more formulas and that would be tedious. How do we split text on each appearance of a specific character?
There's a built in feature to split text into columns. It is called Text to Column feature. Using this feature you can split original text into multiple columns, on each appearance of the separator.

Let's see how you can use it split text based on a specific character.

First thing, have enough empty columns so the right of the source text. If you have data in columns to the right of source text, they may get replaced. It is better to copy the source data in new sheet. In this way, you will have your source data intact and no other data will be affected.

• Select the data that you want to split.
• Press ALT>A>E. This will open Convert Text to Column Wizard box. You can also go to Data> Text to Column, in the ribbon tab.
• Select Delimited and click on next.
  There are few common separator option available to select, like Tab, Semicolon, Comma, Space and Other. You can select one or more separators.
• Here we will split text using comma separator. We can select the comma check box, but here we will use the Other option. In other option, you can write any text to split text at. We will write a comma.

Once you write comma in the text box, excel will preview the output before you finish.

If we had used multiple separator, like space and comma both then we would have split first and last names too.
So yeah guys this how we split text based on a character or text in the string in excel. Both of the methods have their own advantages and disadvantages. In first method you create an dynamic separator that you can use multiple times by just changing the data. But when you have to split text into too many parts, excel formula method can be complex and tedious. On the other hand Text to Column method is good on this part but it is not dynamic.

Let me if you have any doubts or special requirements in the comments section below.

Related Articles:

How To Extract Domain Name from EMail in Excel

Split Numbers and Text from String in Excel

Popular Articles: