Tips‎ > ‎

Combining and Splitting Data in Spreadsheets

posted Nov 17, 2011, 12:12 PM by Jim Greathouse   [ updated Nov 7, 2012, 11:25 AM ]
Google Afternoon Googlers,
Today I want to give you a tip on how to manage data in a spreadsheet.  This is a question I often receive and here are a couple of vary basic steps.

Ever have someone send you a spreadsheet with a list of names?  It seems no matter which way they send it, you need the data in the opposite style.   Either you need it separate as in First Name and Last Name or you need it all together.
Split
This formula splits text based on what divides the test (such as a space, tab, comma, etc. known as a delimiter), putting each section into a separate column in the row.
=split(string, delimiter)

In this example, if you want to split the text contained in the column A, using the delimiter " " (space), we will first need to insert an extra column.  This will insure the formula doesn’t replace what is currently in column B.  With column A highlighted, click Insert then click Column right. Repeat this step a second time to insert a new column C.
Enter the following formula into the column B row 2.    =split(A2," ")  The " " indicates the space between the words.
(the column C will be populated automatically):


Highlight B2 and drag the formula down for all the remaining rows.

Your spreadsheet should now look like the following. 

But notice the data in rows B and C are formulas.  If you delete Column A you will delete the information used in the formulas.  So we have one more step to go.
Highlight the new column of B and C, copy the information then we will use the Paste Special – Paste Value Only feature. 



This will replace the formula with the values.  Now we can delete column A if needed.
The final step would be to give your new columns titles such as First Name, Last Name.


Concatenate
This function concatenates (or combines) several text strings, contained in different cells, into one string.  =concatenate(text1, text2, text3,...)

If you want to concatenate the text contained cells A2 and B2 into one cell, add a new column C, then enter this formula into the cell C2:
=concatenate(A2," ", B2) The “ “, separates each single value with a space.


Notice again the new information is a formula.  
Copy and Paste Special to paste the values only.


This will replace the formula with the values.  Now we can delete column A if needed.
Comments