In all our previous blog posts in this series on Excel we have covered using Formatting and validation on empty cells in order to maintain consistency of newly added data.
These same skills for formatting can also be applied to worksheets already containing data that you wish to change the formatting to a style of your choice, simply highlight the cells to apply the changes to and then follow the steps as before.
However in some circumstances if data has been added incorrectly then formatting or validation cannot just be applied to the cells as shown below:
Example 1 – Extra Spaces
You can see from the example below that the text has been entered but some of this text has spaces either before or after which can cause issues and also appears inconsistent.
In order to correct this you must apply a formula in a separate column to remedy this and then copy and paste the results into the original cells.
- Select a single cell to apply the formula to
- Enter = this allows for a formula to be entered
- Type TRIM
- Next either select or type in the cell reference of the cell containing the data you wish to correct.
- Click OK to confirm
- Once the result appears AutoFill the formula to the rest of the column so that all the data is corrected.
- Copy the corrected data and paste using PASTE VALUES over the existing results
- Using Paste Value will enable the results to take on the formatting of the existing cells you are pasting into.
Example 2 – Capitals/Sentence Case/Lower Case
In this example we can apply the same technique just changing the formula to change the case of data.
- Again select a single cell in a blank column in which to add the formula
- Enter the formula required using either UPPER, LOWER or PROPER
- In this instance we will use UPPER to change the case from Sentence case to all capitals
- Continue as previously and AutoFill the results
- Copy and Paste values as before
The result is shown below
Example 3 – Combination Formula
You may need to correct more than one thing at the same time. You can combine your formula in order to achieve multiple outcomes.
In the example below we will change the Surnames to be Upper case and remove any extra spaces.
- Again select a single cell in a blank column in which to add the formula
- Add the formula as shown
- Continue as previously and AutoFill the results
- Copy and Paste values as before
The results are shown below
If you would like any specialised training for any area of Excel please get in touch with the training team at Fusion to discuss.
Sharon Brook – IT Training Manager – 01484 500767 – [email protected]