• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel CLEAN Formula

You are here: Home / Excel Formulas / Excel CLEAN Formula
Excel CLEAN Formula
July 17, 2013 by Mynda Treacy

Ever copy and paste some data from a web page, or import it from another program, and end up with some funky and usually unwanted characters like these nonprinting characters below?

Excel CLEAN function

The Excel CLEAN Function can help you to remove them, but it has some limitations.

You see it was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. Blah, blah, blah… Sorry, let me give you an example before you fall asleep.

Excel CLEAN Function Syntax

It’s pretty straight forward:

=CLEAN(text)

Where ‘text’ can be a reference to the cell containing the text you want to clean. See the example in cell E2 below:

Excel CLEAN formula

Warning: more boring techy talk coming up, but I know some of you will want to know this stuff.

The CLEAN Function Can’t Do It All

In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157).

Plus if you’re familiar with HTML then you’ve probably stumbled across the HTML entity;   which is character 160.

To remove these you need to use the SUBSTITUTE function.

SUBSTITUTE Function

The SUBSTITUTE function replaces new_text for old_text in a text string.

The syntax is slightly more complicated that the CLEAN Function but nothing drastic:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

The arguments are as follows:

  • text – cell containing the characters you want to replace.
  • old_text – the text to be replaced.
  • new_text – the new text you want inserted.
  • Instance_num – if omitted, it replaces every occurrence of the old_text; Otherwise, only the specified occurrence of the old_text is replaced in the text string.

It can do everything CLEAN can do and more like this:

Excel SUBSTITUTE formula

Taking the formula displayed in cell C4 above, in English it reads:

Locate the text in cell A4, and find the character number 127, and replace it with nothing (as denoted by the two sets of double quotes).

Of course with SUBSTITUTE you first have to know the character number you're trying to clean up. It's just as well then that you can use the CODE function to find this out.

Other SUBSTITUTE Formulas

The SUBSTITUTE function isn't limited to working with the CHAR function. You can use it to replace any text.

For example, let's say we wanted to replace the text 'Iron Man' with 'Captain America' in this text:

Cell A1: Iron Man is the greatest Avenger

Your SUBSTITUTE formula would be:

=SUBSTITUTE(A1,"Iron Man","Captain America")

And now it reads:

Cell A1: Captain America is the greatest Avenger

I'm not sure my 4 year old would agree with my changes so it's just as well he can't read yet!

What About TRIM

Now you might think that the TRIM function can replace blank spaces at the end of text, but unfortunately it can’t handle these special characters.

TRIM is only good for removing the standard space character number 32 from text. But don’t forget it ignores single spaces between words.

Excel CLEAN Formula

More Text Formulas Posts

Excel TEXT Function – handy but limited…or is it?

Excel TEXT Function – handy but limited…or is it?

The Excel TEXT Function converts numbers to text in the format you specify. It's hand for joining numbers and text together in custom chart labels etc.
Extract Text from a Cell using MID Formulas

Extract Text from a Cell using MID Formulas

Excel Test if a Range Contains Text, Numbers or is Empty

Excel Test if a Range Contains Text, Numbers or is Empty

Excel SUBSTITUTE Formula

Excel SUBSTITUTE Formula

The Excel SUBSTITUTE function replaces new text for old in a text string. It's an alternative to using Find and Replace by retaining the original data.
Excel Factor Entry 3 Re-format Data Using Formulas

Excel Factor Entry 3 Re-format Data Using Formulas

Use Excel's Text functions to re-format data imported from external sources
Excel SEARCH and You Will FIND

Excel SEARCH and You Will FIND

Excel SUBSTITUTE Function Trick

Excel SUBSTITUTE Function Trick

Excel TRIM Function Removes Spaces From Text

Excel TRIM Function Removes Spaces From Text

Excel TRIM function removes spaces from the start and end of text, while leaving spaces between words untouched.
Excel UPPER LOWER and PROPER Functions

Excel UPPER LOWER and PROPER Functions

Microsoft Excel’s T Function

Microsoft Excel’s T Function

The Excel T function checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it isn’t.

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel FormulasTag: text formulas
Previous Post:Excel SUBSTITUTE FormulaExcel SUBSTITUTE Formula
Next Post:Excel Formulas Not Working?Excel Formulas Not Working?

Reader Interactions

Comments

  1. moiz

    July 23, 2013 at 12:16 pm

    Thanks for the tip.Can the substitute function be used to change letter case in XL? If not, is there an alternate way?-moiz

    Reply
    • Mynda Treacy

      July 23, 2013 at 10:13 pm

      Hi Moiz,

      To change the case of letters you need to use:

      UPPER – all upper case.
      LOWER – all lower case.
      PROPER – for the first letter in each word to be capitalised.

      Kind regards,

      Mynda.

      Reply
  2. Piotr

    July 19, 2013 at 6:47 pm

    Good to know about characters which are blank spaces – thanks!
    Once upon a time I had an adventure with character 160 when the data were taken from some accounting software…

    Reply
    • Mynda Treacy

      July 19, 2013 at 7:27 pm

      🙂 You’re welcome, Piotr. I can only imagine the fun you had detecting 160!

      Reply
  3. TomD

    July 17, 2013 at 9:19 pm

    Learned a lot — thanks a bunch!

    BTW — what was the function you used in columns B & E to get the length? I’m still learning.

    Tom D.

    Reply
    • Mynda Treacy

      July 17, 2013 at 9:57 pm

      Hi Tom,

      Thanks for your kind words.

      The function to get the length is LEN. It goes like this:

      =LEN(A1)

      and it’ll return the length of the text in cell A1.

      Too easy 🙂

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.