From the Not Just Numbers blog:
I hope everyone’s enjoyed the Easter Break.
Just a quick and easy tip this week to ease us all back in gently!
I’ve written a number of posts in the past about tidying up text using functions, e.g. (Simple tips for tidying up text in Excel and Removing all spaces from text), but I thought I’d give a quick mention to a useful tool for a one-off tidy-up that can be very powerful (and also works within formulae).
The Find and Replace tool appears throughout Microsoft Office, but within Excel it can be even more powerful.
If you click the binoculars on the Home ribbon and choose Replace, you are presented with the following Dialog box:
- It will include text within the cells (it does not need to be the whole cell contents)
- It will look within formulae too
- It is not case sensitive (however the replacement text will use the case you have entered)
- Remove all spaces from text, by entering a space in the top box and nothing in the second box
- Replace an incorrect reference in a range of formulae, e.g. replace $A$1 with $B$6
- Remove commas from address data to make it usable as a csv file (e.g. replace , with space)
- Correct an incorrect formula, e.g. replace < with <=
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.