Search Not Just Numbers

Tuesday, 5 February 2013

Excel Tip: Simple tips for tidying up text in Excel

Although we might do our best to use data validation to ensure text is correctly entered into a spreadsheet, we very often need to deal with data from other sources that might not be well controlled.

Often one of the biggest issues in dealing with data to make it useable is tidying up text, so that names, departments, addresses, etc.are entered consistently.

Once this tidying up exercise is done, these text fields become useful for filtering the data using autofilter, or pivot tables - or for looking up information from other lists.

A simple approach to take is to work on creating a formula that deals with most of the problems, then copying this down alongside the text list. Then, when we are happy, we can copy this column and paste special (as text) where we want the text to be - if this is a one-off exercise - alternatively we can use the calculated column to drive the reports if it needs to handle new data being added in future.

Excel provides a number of tools that can help to clean up the data.

Removing extra spaces

One of the particularly sticky problems with entered text is extra spaces - particularly because they are so hard to spot. Excel provides a nice simple solution to this, the TRIM function.

=TRIM(text) strips all spaces out of a text string, except single spaces between words.

text can be actual text which must be enclosed in quotation marks (i.e."text") or, as we would use for this purpose, a cell reference containing the text (e.g. A1).

That means that it will remove any spaces at the beginning or end of the text, as well as ensuring that any spaces between words consist of only one space. If they include more than one space, the extra spaces will be removed.

Correcting the case

Particularly where data is to be presented in some way, one big concern can be what case the text is using. This may be all on capitals (rarely looks good on a report), or worse - it might be an inconsistent mix of capitals and lower case.

Excel has three functions to handle this.

=LOWER(text) presents the text entirely in lower case, irrespective of its original case.

=UPPER(text) presents the text entirely in upper case, irrespective of its original case.

=PROPER(text) presents the text in lower case with a capital letter at the start of each word, again irrespective of its original case.

Comparing to valid data

Where the above functions might remove some relatively standard problems, we will often still have to do some editing to completely tidy the list. The time spent doing this can be considerably reduced if we can get Excel to tell us which ones we need to correct.

One of the simplest ways to do this is to use COUNTIF to compare the text to a list of valid options (almost retrospective data validation).

COUNTIF counts the number of instances in a range that meet certain criteria.e.g.

=COUNTIF(Sheet2!$A:$A,B1) counts the number of cells in column A of sheet 2, that contain the value (or text) contained in cell B1.

If column A of Sheet2 contained our list of valid values and column B of the current worksheet contained the text we wanted to correct, then all of the invalid entries will return a 0, which we can then filter the list by to show all of the rows that need correcting.

Happy tidying!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".


  1. Kathie Gordon-Brooks5 February 2013 at 16:23

    I have been struggling with all upper and all lower case in the same report. Thank you for this tip!

  2. Kathie. Pleased it's sorted your problem. Sometimes it's the simple tips that can make the biggest difference!

  3. The manufacturers of replica watches Rolex watches apperceive that their latest watches are getting replicated and to abstain accident of business they accomplish attenuate and accomplished changes to their ambit of watches.This is a cat and abrasion bold and as anon as changes are fabricated in the original,a few down the line,they are congenital in the Swiss replica watches as well.So what does one angle to accretion if they are purchasing Swiss replica watches? For one the budgetary accumulation is obvious.Afar from that there is bell ross replica the accord of apperception too.If anyone is mugged and their aboriginal Swiss watch is burgled,they accept a lot to anticipate about.Not so for those who accept Swiss replica watches on them.They can simple go out and acquirement addition Swiss replica watches the next day.Replica handbags awash in food will accept to gucci replica anguish about patents and copyrights.However,the internet has brought a absolutely new ambit to that.Added than replica handbags,replica items can be calmly begin on the internet.There are actually accoutrements of fashionable replica handbags sellers commercial their articles on the internet.Some even action cash-back offers.Manufacturing of these replica accoutrements takes abode in Far East.China has become a arch supplier in this regard.In fact,some of the replica handbags are so acceptable chanel replica that even backpack experts cannot acquaint the difference.These manufacturers are acceptable at authoritative apery replica handbags.The material,the adroitness and the account is so acceptable that it is actual difficult to differentiate amid a 18-carat fashionable backpack and the replica.Women who buy replica handbags are absolutely afraid at dior replica the top superior replica handbags they get.This lures them to acquirement added of them.In addition,they adjustment analogous shoes and added dior replica.dior replica is to women one of those allegorical names,a name with bewitched connotation,apery dream artist handbags.

  4. Mohon ijin berpromosi. service Ac segala jenis merk. kami service Ac mencakup wilayah coverage seluruh Surabaya, Gresik, Sidoarjo. kami juga menerima jasa service perbaikan AC bongkar dan pasang AC. pekerjaan kami cepat, tepat, profesional, harga kompetitif di banding harga jasa luaran.
    service ac split sidoarjo
    tukang service ac sidoarjo
    service ac surabaya sidoarjo
    service ac surabaya selatan
    service ac surabaya timur
    service ac surabaya barat
    service ac surabaya utara
    service ac kota surabaya


    Segera hubungi kami, dan kami akan datang kerumah Anda 24 Jam Non Stop.
    (Hari Sabtu / Minggu / Hari Libur buka)
    PIN BBM : 54BE0B4B / 5474CD93 / TELP : 085645475574

    Martabak Paling Enak di Jakarta
    Tempat Tidur Minimalis
    Tempat Tidur Minimalis

  5. This comment has been removed by the author.