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.