How To Split The Contents Of A Cell

Spreadsheets,Tips 11 March 2011 0 Comments

File this one under useful formulas: sometimes in a spreadsheet we want to split the contents of a cell. For example, perhaps the you have pasted a list of names into a column, like “John Doe”, but what you really want is the first names in one column and the last names in another. This can easily be achieved using the split() function:

  • Set the cell equal to the value of the split() function where the first parameter is the text or a reference to the cell whose contents you wish to split, and the second parameter is the delimiter, i.e. the character upon which you will split the contents of the cell. For example, to split the contents of cell A2 at each instance of a space character, you would type in the following formula:
=split(A2," ")

Notice that the cell is split into a new cell for every occurrence of the delimiter. So, in the above example, since there are two space characters in A2, the contents will be split into three cells (B2, C2 and D2). What if there are multiple characters which act as delimiters? Easy:

  • Set the cell equal to the value of the split() function like the last example, but for the second parameter, include all of the delimiters. For example, if you want to split the contents of A2 on every occurrence of a space, underscore, or comma, use ” _,” as the second parameter to the split() function like so:
=split(A5," _,")

To review how the split formula works on an actual Google spreadsheet, you can access the spreadsheet used in the above examples here.

Tagged in , , , ,

Leave a Reply