Tuesday Tip – Divide and Conquer: Columns in Excel

by Cheryl Sklar

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:

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.

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.

 
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.

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.

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

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)

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

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!

Cheryl 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-old, IT 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.