Extract text before a space

Often when data has been downloaded in Excel there is information in a cell that you would like to split in to two different cells so that you can analyse the data further.

Take the simple example of a list of forenames and surnames that are listed in a single column.

If you want to sort this list by forename this is fine, however to sort by surname you will need to split the data in to two cells.

Using the FIND formula, together with LEFT, RIGHT or MID will allow you to easily split and extract data.

Below is an example where a persons full name is in column A.

Using the formula:

=LEFT(A2,(FIND(" ",A2,1)-1))

it will allow you to extract data up to the first space (Stephen in this case).

It's also simple to extract the surname, all you would so in this example is change LEFT for RIGHT to extract Bailey.

The fomula is easily modified if, for example, you have a comma, colon, etc... instead of a space.

A simple but useful formaula for extracting data in one cell in to two or more cells.