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:

=B2&”,”&A2

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.

 

 

1 Comment

  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