Excel Tip: Using Find and Replace to tidy up text (or formulae)

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:

This (by default) will search through the area of the worksheet you have selected (or the whole worksheet if you have not selected a range of cells first) and replace the text you enter in the first box with the text you enter in the second.

The following rules apply (assuming you do not change the options):

• 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)

These rules can be changed by making the options visible by clicking the Options >> button.

Here are some examples of how this tool can be used:

• 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 <=

With a bit of thought, many time-taking corrections can be speeded up considerably.

Before I go, I have a couple of updates to bring to your attention.

Firstly, if you haven't already given your answer in the poll at the top right regarding the version of Excel that you use, please do. At the time of writing, 16% of those responding still use Excel 2003, but that may be skewed by the topic of last week's post attracting more Excel 2003 users!

Secondly, Mynda Treacy's excellent Excel Dashboards course is once again available and, until 1st May, you can not only get a 20% reduction, but a free copy of my Introduction to Pivot Tables course absolutely free (just enter "Feechan" in the Referral Source field when you purchase, and email me a copy of the receipt.

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".