Win £12,000 at TRA Golf Day
February 2, 2018
MTD for VAT reporting
January 2, 2018
Excel Custom Number Formats
September 22, 2017
August 25, 2017
Excessive Rows (and/or Columns)
August 11, 2017
Printing - Multiple Pages from a single worksheet
July 28, 2017
Formulas not calculating
July 14, 2017
June 30, 2017
Drop Down List Defaults to Middle or Bottom?
June 16, 2017
Extract text before a space
June 2, 2017
DOUBLE CLICKING TO A FORMULA
November 20, 2016
2 Jun 2017
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:
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.
Formula Not Updating
Multiple rows of data
Visible Cells Only
February 2018 (1)