Jul 25, 2014
When data is imported into Excel, excess characters sometimes come with it. Non-printing characters are the hardest to detect as they are, of course, invisible. Spaces are the most common of the non-printing characters. Excel has a worksheet function called TRIM that can remove these unwanted spaces. It will remove spaces from before and after values, and also excess spaces (i.e. more than one) between words. The above TRIM function is designed to remove the 7-bit ASCII space character (value 32). In basic English, it removes the character made by using the space bar on your keyboard. There is another character called a non-breaking space (value 160, which is often found in web pages) that TRIM doesn’t remove. To get rid non-breaking spaces, the SUBSTITUTE function in conjunction with the CHAR function can be used.- The CHAR function refers to a character by its value. For example ‘=CHAR(32)’ is a space and ‘=CHAR(160)’ is a non-breaking space.
- The SUBSTITUTE function replaces one string of text with another.
=SUBSTITUTE(CELL REF,CHAR(160),CHAR(32))If the aim was to replace non-breaking spaces with normal spaces, but ensure that there were only one normal space between words, the formula would read:
=TRIM(SUBSTITUTE(CELL REF,CHAR(160),CHAR(32)))Space may be the final frontier, but that doesn’t mean it has to clutter up our spreadsheets! For more on functions that can manipulate text, see the New Horizons’ Excel 2010 Level 4 training course.
How do your Excel skills stack up?
Test NowNext up:
- Get your head into the cloud for free!
- Returning multiple lookups using Excel’s VLOOKUP
- Assertiveness – From theory to practice
- Become an ace in Microsoft Azure SQL Database
- Creating multiple signatures in Outlook
- Get online with Lync Online
- Keep your Excel formulas in place with dynamic named ranges
- MH-17 and words
- Run the Runbook Tester in System Center 2012 R2
- Easily delete blank rows from your data using Excel VBA
Previously
- Crash course in Microsoft Azure SQL Database
- Interviewing and avoiding the artful dodger!
- Access your Access files in Microsoft Project
- Networking architecture in Lync Server 2013
- 10 essential keyboard shortcuts in Photoshop
- Remove blank rows in Excel with this VBA code
- Becoming a great workplace trainer starts with three words (Part 3)
- Cross-site publishing with SharePoint 2013
- Easily convert dates to Australian format in Excel
- Use SCCM 2012 R2 to manage Linux machines