Applicable to: Office 2019, 2016, 2013; 365 or standard

Here’s a question that we have received from a reader who wanted to divide an Excel column vertically:

I am working on a statistical report in Microsoft Excel, using version 365. I just imported a comma separated value file into the A column and now need to split a column cell vertically into two “halves”. Any simple way to get that accomplished? If it helps i am using Windows 10.

How to Use “Text to Columns” in Excel Select the cells you want to split by clicking the first cell and dragging down to the last cell in the column. In our example, we’ll split the first and last names listed in column A into two different columns, column B (last name) and column C (first name.) The Best Tech Newsletter Anywhere. Select the cell you want to split. You can select multiple cells at the same time if you wish. Select the Data tab and choose the “Text to Columns” option. Choose “Delimited” if prompted. Select the type of delimiter (s) your cell uses. Use the preview pane to see if your cell’s data is splitting correctly. Select the cells you want to split by clicking the first cell and dragging down to the last cell in the column. In our example, we’ll split the first and last names listed in column A into two different columns, column B (last name) and column C (first name.) Click the “Data” tab at the top of the Excel Ribbon.

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. You can use the LEFT, MID, RIGHT, SEARCH, and LEN text functions to manipulate strings of text in your data. For example, you can distribute the first, middle, and last names from a single cell into three separate columns. The key to distributing name components with text functions is the position of each character within a text string.

Thanks for the good question. For these scenarios i typically use the Text to Column feature, which serves my purpose pretty well.

Sometimes we import significant amount of data directly into a worksheet or into the data Model (Using Power Query and Power Pivot in Excel). Typically, we import so called comma separated value files files (*.csv). When working with csv files, we typically find ourselves in need of vertically dividing or splitting text between different column cells. In today’s post we’ll cover three useful techniques that you will be able to use:

• Text to Columns for delimited files
• Text to Columns with Fixed Width
• Un merging cells

Read further for the full procedure – which applies with minor variations to Microsoft Office versions starting 2010.

Using the Excel delimiter for splitting text

This technique is specially useful when you are working with a worksheet that contains data which is delimited by commas , semi colons, tabs, spaces and so forth.

• Open your Excel and open the database in which the Columns has to be separated.
• If you have data in the next cell, select the cell and right click on it.

• Click on Insert and then go ahead and select Shift Cells Right.

• Now, select the entire column which will need to be separated.
• Click on Data in the ribbon and then go ahead and select Text to Columns.

• Select Delimited and click Next.

• Click on any one of the option in the Delimiters and select Next.
• Say for Example I have selected Space for separation.

• In the Column Data format Select your option if needed. It will be General as Default.
• To select in which cell the separation has to be done, Click on Destination and select the cells.

• After you have done with the selection, click Finish.

Dividing to Columns using Fixed width

• Open your spreadsheet and select the column cell which has to be divided.
• Again make sure no data is available next to the dividing cell.
• In your Excel header menu, click on Data and select Text to Columns.
• Select Fixed width and Click on Next.

• For creating a break line, click on desired position
• For deleting, double click on the break line.
• For moving, click and drag the break line.

• In the Column Data format Select your option if needed. It will be General as Default.
• To select in which cell the separation has to be done, Click on Destination and select the cells.
• Click Finish after selecting your format.

Un merging cells

• To split merged cells, click on Merge & center and select Unmerge cells.

Horizontal split cells in Excel rows

Unlike when working with tables in Microsoft Word, PowerPoint and Outlook; there is no straightforward Split command for achieving horizontal splits in Excel row cells. Readers wanting to horizontal split cells could use specific formulas to divide their table data across two or more table cells in their worksheet.

Hope it helps 🙂

How to format mac addresses in cells by adding colon symbol in Excel?

Supposing you received a worksheet contains a list of numbers, and now you need to format all these numbers as mac addresses by adding colon symbol as below screenshot shown. What can you do to achieve it? You can try the method in this tutorial to solve the problem.

Format mac addresses in cells by adding colon with formula
Format mac addresses in cells by adding colon with VBA
Easily format mac addresses in cells by adding colon with Kutools for Excel

Format mac addresses in cells by adding colon with formula

The below formula can help you format number as mac address by adding a colon. Please do as follows.

Excel For Mac Split Cells

1. Select a blank cell, enter formula into the Formula Bar, and then press the Enter key. See screenshot:

=LEFT(A2,2)&':'&MID(A2,3,2)&':'&MID(A2,5,2)&':'&MID(A2,7,2)&':'&MID(A2,9,2)&':'&RIGHT(A2,2)

Note: In the formula, A2 is the cell contains the number you will format as mac format.

2. Select the first result cell, drag the Fill Handle down to get all mac format numbers. See screenshot:

Format mac addresses in cells by adding colon with VBA

With below VBA code, you can format a range of numbers as mac addresses at once in Excel. Please do as follows.

1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the code window.

VBA code: Format numbers as mac addresses in cells

Excel Mac Split Cell Diagonally

3. In the opening Kutools for Excel dialog box, please select the cells with numbers you will format as mac addresses, and then click the OK button.

Then you can see all selected numbers are formatted as mac addresses as below screenshot shown.

Easily format mac addresses in cells by adding colon with Kutools for Excel

This section will introduce the Add Text utility of Kutoos for Excel. With this utility, you can quickly add colons to cells at specified positions. Please do as follows.

Split Excel Cell In Half

Before applying Kutools for Excel, please download and install it firstly.

1. Select the cells you need to format as mac addresses. And then click Kutools > Text > Add Text.

2. In the Add Text dialog box, please type a colon into the Text box, select the Specify option, and then enter the specified position numbers into the box. And finally click the OK button.

Note: In this case, I need to add colons after every two numbers in cells, so I enter 2, 4, 6, 8 and 10 and separate them by commas. See screenshot:

Split Cells Excel

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Related articles:

Excel Split Cells In Row

Excel Split Text Into Cells

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• To post as a guest, your comment is unpublished.
  You can Format Mac Addresses using stringlearner.com. Much easier.