Wednesday, February 13, 2013

Spreadsheet formula for separating names

Have a column of student names you want to sort but they are entered in as "Andy Schwen"?

Here's a formula that I just used that is helpful in grabbing just the last name:

=iferror(right(B2,len(B2)-find(" ",B2)))

Breaking it down:

find(" ",B2)
The find part of the function looks for the space in the text in cell B2. If it wasn't a space but a comma instead you could use "," instead for that part. 

The len(B2) part finds the total length of the string.

len(B2)-find(" ",B2)
By putting those parts together we take the total length, subtract the # in the text string where the space occurs and then we take the right number of characters in the text using the number that was just calculated.

The iferror part added at the beginning just keeps the cell blank if there is nothing to calculate.

1 comment:

  1. Andy, this is Mark Carlson we met in Chicago this summer. Nice job I was looking for a way to do this and I stumbled upon your answer. Nice to see you are still doing great work.