Concatenate & Split
Concatenate
Concatenate formulas take data from different columns and joins them together.
Earl E. Bird becomes Bird, Earl E.
- add a column for the combined names
- In the cell where you want the combined name to appear, enter: =CONCATENATE(C2, ", ",B2)
- that is: equals CONCATENATE
- parentheses column-row of first item to be added to the string, in this case C2, then separate with a comma
- to have a comma appear between last name and first, enter: quotation mark–comma–space–quotation mark. Then separate with another comma. It looks like this: ", ",
- If you're concatenating text, it must be in quotation marks. Numbers don't need quotation marks.
- column-row of second item–close parenthesis, in this case B2
- enter
- select the cell, then move the mouse to the bottom right corner–mouse becomes a plus sign
- drag the plus sign to the end of the column to copy the formula down
- Now everything looks great! But there's another step if you want the name to appear correctly in a merged document:
- Copy all the data you just created with your CONCATENATE formula in the new column, then delete it. Trust me!
- With the new column now blank, under Edit, choose Paste Special> Paste values only
- Now the names appear in each cell, but the formula does not.
Split text to columns
"Split" takes two or more words in a cell and inserts each into a new column.
Bird, Earl E. becomes Bird Earl E.
- Select the whole column you want to split by selecting the header.
- Under Data, select "Split text to columns".
- Sheets will remove the multiple word text and replace it with the first word only, then add a column for the second word.
- "Split" is a lot easier than Concatenate.
Copy the CONCATENATE formula down the column by dragging the plus sign.
The screenshot below shows how to concatenate to create an email address.
Other Add-ons for sheets:
copyDown is a spreadsheet add-on will copy a formula down the column as each row is filled. This is helpful for things like totaling scores that you submit on a form. BTW, just like autoCrat, copyDown was created by CloudLab New Visions for Public Schools
Save as Doc is an addon for sheets that allows you to take long, open-ended questions on a form, and make them a little easier to read by adding it to a document.