Search Not Just Numbers

Tuesday, 22 April 2014

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.

Excel Dasboard Course

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


  1. Why would you want to remove commas from a comma spaced values file (.csv) "to make it useable" when it already is useable?

    1. If you are creating csv file from existing data that has commas where it shouldn't. I have come across this in address data for example, where (on some of the addresses) someone might have put a comma after the house number in the first line of the address.

  2. Some other preference on mom's expensive jewelry is known as a mother's's band. Captivating charm-hublot replica sale charms help ones own the mother put a enchantment per each in the infants and even it can also be ones own birthstone as well as a distinct thing which will reminds the of these individual infant. And lastly, there are various expensive jewelry the right gifts designed for folks that will deliver ones own rolex replica & granny an important have fun and even show the how i believe. Neck laces is a loved pick regardless if you can get an important mom's necklace around your neck maybe a amazing locket which may accommodate ones own envision thus it will be able to stay in approximately the core. Moms can be rolex replica and even we're able to not even end up just who efficient while not them all. The perfect product will help uou talk about the level of you like ones own folks. Hunting online will help uou acquire a lot of these top notch some new season expensive jewelry the right gifts designed for the mother & granny. Take pride of place hublot replica sale around wearable concept on 2014. Bright running watches, Google and yahoo Tumbler and even FitBits result a whole lot of technician posts. Nonetheless not everybody is without a doubt thoroughly for sure in cases where they should contain commonplace worldwide recognition, just a few workforces which includes firefighters, manufacturing workforce and even police are looking at with them on the job. It begs that question--why are usually not THE APPLICATION sectors hublot replica uk the same principal? Not surprisingly, THE APPLICATION sectors are frequently identified through inventive internet business habits which will utilize most recently released know-how. Why not even wearables?