Tuesday Tip – Divide and Conquer: Columns in Excel

by Cheryl Sklar

Divide and Conquer Columns in Excel1







While these are really cool architecturally, they are not the columns that we’re talking about today.  While in an Excel worksheet (especially if you’re importing information from somewhere else), have you ever tried to either divide one column of information into two or merge 2 fields of information into one column?

To Divide One Column into Multiple Columns of Information:

For example:

Divide and Conquer Columns in Excel2



Go to Data > Text to Columns, and the wizard will walk you through the process. Here’s a full breakdown of how it works:

Select the cell or column that contains the text you want to split.
Note:   Select as many rows as you want, but no more than one column. Make sure there are enough empty columns to the right so nothing over there gets overwritten. If you don’t have enough empty columns, add them.
Click Data >Text to Columns.

Divide and Conquer Columns in Excel3



This starts the Convert Text to Columns Wizard. Click Delimited > Next.
Check Space, and clear the rest of the boxes, or check Comma and Space if that is how your text is split (Smith, John, with a comma and space between the names). You can see a preview of your data in the Data preview window.

Divide and Conquer Columns in Excel4




Click Next.
In this step, pick the format for your new columns, or you can let Excel do it for you. If you want to pick your own format, select the format you want, such as Text, click the second column of data in the Data preview window, and click the same format again. Repeat for all the columns in the preview window.

Divide and Conquer Columns in Excel5







Click the   button to the right of the Destination box to collapse the dialog box.
Select the cells in your workbook where you want to paste your split data. For example, if you are dividing a full name into a first name column and a last name column, select the appropriate number of cells in two adjacent columns.
Divide and Conquer Columns in Excel7






Click the   button to expand the dialog box, and then click Finish.

Divide and Conquer Columns in Excel9


To Merge Multiple Columns of Information into One Column:

This function is called Concatenation, which, according to Dictionary.com is defined as a series of interconnected or interdependent things or events.

If you want to take Column A and Column B and put them together into Column C, you create a formula like this:

=A2&” “&B2

This gives you First name (column A) then a space and then Last name (column B)

Divide and Conquer Columns in Excel10.jpg







Whatever you want between the two values should be in quotes.  For example, if you want to put a comma in between Last Name, First name, it would look like this:


Divide and Conquer Columns in Excel11




So while you’re manipulating spreadsheets of information for your medical practice or law firm, these Excel functions can really save you a lot of time.  For more helpful Column tips, see Richard’s previous tip about Swapping Columns and Rows with Ease, or David’s Excel Conditional Formatting.  You’ll be an Excel expert before you know it!



12-175Cheryl Sklar  With over 25 years of experience in sales,  marketing and general coordination, Cheryl uses her varied organizational skills to enhance the behind the scenes workings of Network 1.  She strives to help things run smoothly wherever she can.

csklar@network1consulting.com or 404.997.7641


Network 1 Consulting is a 16-year-oldIT Support company in Atlanta, GA.  We become – or augment – the IT department for law firms and medical practices.  Our IT experts can fix computers – but what our clients really value are the industry-specific best practices we bring to their firms.  This is especially important with technology, along with regulations and cyber threats, changing so rapidly.  We take a proactive approach to helping our clients use technology to gain and keep their competitive advantage.



Tony Rushin Headshot

Tony Rushin: In his role as VP of sales & marketing, he is responsible for hiring, managing and coaching the sales team and always staying on top of (and meeting) the needs of clients. Under his leadership, the company has steadily grown each year.

Network 1 designs, builds and supports the IT you need to run your business more securely, productively and successfully. Whether you want to outsource all of your IT needs to a reliable, responsive, service-oriented company, or need to supplement the work of your internal IT staff, we will carefully evaluate where you are now, discuss where you want to go and implement and support a plan to get you there with as little interruption as possible.


  1. […] If you missed out on some of our previous ones, here are a few for your reading pleasure:  Divide and Conquer: Columns in Excel, Excel Conditional Formatting and How to fix a Common Excel […]

Leave a Comment

Related Posts