CBA Sage (Peachtree) Accounting Support Logo



Linkedin Rss Gmail


Buy Sage 50 2022 Sage Certified Master Consultant logo

Menu
  • Home
  • Products
    • Sage 50 2023
      • Sage 50cloud Pro 2023
      • Sage 50cloud Premium 2023
      • Sage 50cloud Quantum 2023
      • Quantum Manufacturing 2023
      • Feature Comparison 2023
      • Sage Subscription FAQ 2023
    • Sage 50 2022
      • Sage 50cloud Pro 2022
      • Sage 50 Pro 2022
      • Sage 50cloud Premium 2022
      • Sage 50 Premium 2022
      • Sage 50cloud Quantum 2022
      • Sage 50 Quantum 2022
      • Quantum Manufacturing 2022
      • Feature Comparison 2022
      • Sage Subscription FAQ 2022
    • QuickBooks
    • Sage Business Care
    • Logicim XLGL
    • Checks & Forms
    • Why Sage 50?
    • Merchant Services
    • Add-Ons from Sage Partners
    • Sage 50 Free Trial
  • Services
    • Sage 50 Custom Reports
    • Consulting
    • Training
      • Training - Create your own Class
      • Webinars
    • Bookkeeping
    • Support
      • Technical Support
      • Implementations
      • Rebuilds & Repair
      • BI Reports
      • Custom Financials
      • Crystal Reports
    • Merchant Services
    • Custom Macros
  • Resources
    • Testimonials
    • Video & Tutorials
      • Sage Advisor
      • Sage 50 Tutorials
    • CBA Blog
    • EXCEL TIPS
    • 2023 System Reqs
    • 2022 System Reqs
    • Downloads
    • Newsletter Signup
    • Ask an Expert Form
    • Sage FAQ
      • Whats New In Sage 50?
    • Install Info
  • About Us
    • Why Buy from CBA?
    • Customer Success!
    • Careers With CBA
    • States We Serve
  • Contact
  • Site Map
CBA Green Bar
Resources > EXCEL TIPS

Welcome to the Computerized Business Blog please feel free to look around.


How to Remove Stubborn Spaces and Characters in an Excel Cell

22
9/22/2015

If you have ever had problems sorting, filtering, or manipulating data in a Microsoft® Excel® workbook and realized that it was caused by stubborn leading spaces in the cells, the following steps will show you how to get rid of the spaces quickly next time.

If you’re wondering what causes the spaces, here are two possibilities:

  • You may have mistakenly inserted extra spaces in your data by pressing the space bar more than once; or
  • You may have imported data from external sources which has embedded characters in the text that may not be easily noticeable making the results difficult to understand.

The next time you have this issue you can quickly remove unwanted spaces and characters using a combination of the TRIM and SUBSTITUTE functions. The TRIM function removes leading and trailing spaces in data but not characters. The SUBSTITUTE function replaces non-breaking characters with characters that the TRIM function can remove.

1. To remove leading and trailing spaces from the example workbook; the TRIM function will be used.

a. Select cell C2.
b. Type the formula =TRIM(B2).
c. Press Enter and copy the formula down.

2. As you can see the spaces have not yet been removed. This is because the TRIM and SUBSTITUTE functions must be nested.The stubborn characters set number 160 will be replaced with 32 then can be removed by the TRIM function.

Note:

  • In order to transmit information between computer systems a standard system that assigns numbers to letters of the alphabet, digits and characters was developed.
  • Therefore the character set number 32 represents a space.
  • The character set 160 represents a non-breaking space character that can’t be removed by the TRIM function. It is commonly used in web pages.

tip-1

a. Select cell C2 and delete the formula.
b. Enter the following formula and then copy it down:

=TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))

tip-2

The stubborn leading spaces and characters will be removed so you can easily manipulate your data. Essentially, the SUBSTITUTE function in our example replaced the non-breaking character with a space which the TRIM function can remove.

(Appies to Microsoft® Excel® 2007, 2010 and 2013)


By Tim Hambsch
Format spacing in Excel
Spacing issues in Excel
Remove Spacing in Excel
Remove Characters in Excel

FREE e-book: Excel PivotTables and PivotCharts

17
9/17/2015

11 tips on Excel PivotTables and PivotCharts!

A PivotTable can help you extract more meaningful information from your Sage 50 data. It’s an interactive table that quickly summarizes large amounts of numeric data, which you can then analyze in detail using a PivotChart.

This useful e-book from Sage 50 Intelligence provides you with 11 tips and tricks to help you make the most of PivotTables and PivotCharts, giving you the ability to  analyze and present your Sage 50 data professionally.  The tips are focused around the following topics:
  • Slicers
  • Trendlines
  • Timelines
  • Rank Smallest to Largest
  • PivotTable Groups
  • PowerPivot add-in

    DOWNLOAD NOW!

By Tim Hambsch
Sage 50 Reporting
Excel Tips
Sage Intelligence and Excel
PivotTables and PivotCharts



Rss-Logo Subscribe in RSS
Email
Subscribe by Email


Feeds
CBA Blog
EXCEL TIPS & TRICKS

Latest Posts
Accounting Excel Tip: Formatting Spreadsheets ..
How to Extract a List of Named Ranges in Excel..
How to Remove Blank Cells in Your Excel Spreadsheet..
How To Easily Hide Zero Values in Excel..
Remove Hyperlinks from an Excel Worksheet..

28
Posts by Date
 2019
September (1)
•Accounting Excel Tip: Formatting Spreadsheets
August (1)
•How to Extract a List of Named Ranges in Excel

 2017
September (1)
•How to Remove Blank Cells in Your Excel Spreadsheet
January (1)
•How To Easily Hide Zero Values in Excel

 2016
November (2)
•Remove Hyperlinks from an Excel Worksheet
•Use the Excel MROUND Function to Round Off Time Values
September (1)
•How to Rearrange Data Table Columns in Excel
May (2)
•How to Calculate a Deposit or Down Payment in Excel
•Easily Generate PivotTables on Separate Excel Worksheets
April (2)
•Learn Some New Features In Excel 2013
•Create an Excel Combination Chart From Scratch
March (1)
•Learn How to Use the Goal Seek Feature
February (1)
•How to Calculate Multiple Results in Excel by Using a Data Table
January (1)
•How to Create a Waterfall Chart

 2015
December (1)
•Excel Tip: How to Change Upper/Lower Case Text in Excel
November (3)
•Common Microsoft Excel Errors and How to Fix Them
•Link an Excel Worksheet Name to a Cell Using the MID and FIND Functions
•Round off Time Values in Excel Using the MROUND function
October (1)
•Minimize Data Entry Errors by Utilizing the Calendar Drop Down List
September (2)
•How to Remove Stubborn Spaces and Characters in an Excel Cell
•FREE e-book: Excel PivotTables and PivotCharts
August (1)
•Excel Tip: Creating First and Last Name Columns from a Full Name Field
June (1)
•Excel Tip: Editing the Same Cell in Multiple Worksheets
April (1)
•Using the TEXT Formula for Sage Intelligence Reporting
February (1)
•Calculating the Frequency of Specific Values Within a Range of Values
January (1)
•FREE WEBINAR: EXCEL TIPS FOR BETTER BUSINESS REPORTS

 2014
August (1)
•DATEDIF and TODAY Functions Combined
June (1)
•Excel Tips: Conditional Formatting… Finding What You Need in a Pile of Data

 2013
August (1)
•Using Slicers in Excel 2010
May (1)
•Using the TRANSPOSE Function in Excel
January (1)
•Using the DSUM Function to Add Based on Multiple Criteria

 2012
October (1)
•VLOOKUP with the Approximate Match
September (1)
•Sharing Slicers Between PivotTables in Excel 2010
August (1)
•Using a Data Entry Form in Excel
July (1)
•How to use the SUBTOTAL Command
May (1)
•How to use the SUMIFS function in Excel
April (1)
•How to Display Filtered Pivot Table Data on Separate Worksheets


Tag Cloud
  • Sage 50 intelligence training and excel
  • Sage 50 excel tips
  • Excel 2013
  • Sage 50 reporting
  • Peachtree reports
  • Name formula columns in excel
  • Format spacing in excel
  • Using excel for sage 50 reporting
  • Calculate in excel
  • Move excel data table
  • Excel combination chart
  • Sage business intelligence
  • Excel goal seek
  • Calculate days in excel
  • Excel waterfall chart
  • Remove spacing in excel
  • Excel 2013 and 2016 tips
  • Pivottables and pivotcharts
  • Change text in excel
  • Editing text in excel worksheets



Share
RSS feed
Atom feed
Bookmark
Google
Delicious
Digg
MySpace
Facebook
Diigo
Yahoo
Stumble
Reddit
My Aol
LinkedIn

Bookmark and Share
  Powered by WorldWide Merchant


© 2023 Computerized Business blog all rights reserved.


CBA Green Bar

  • Home
  • Products
  • Services
  • Resources
  • About
  • Contact
  • Sage 50 2023
  • Sage 50 2022
  • VersAccounts ERP
  • Checks & Forms
  • Sage 50 Support
  • Peachtree Support
  • Tech Support
  • Sage 50 & Peachtree Training
  • Management Consulting
  • Bookkeeping
  • Merchant Services
Computerized Business Associates
 
1009 Northwood Drive
Galesburg IL 61401
480-945-4455 or 800-339-8224
info@computerizedbusiness.com
Sign Up For Our Newsletter!

CBA Peachtree Newsletter Signup Image
Get tips and news from Sage 50 (Peachtree) experts in your inbox
Master Certified Sage Consultants
Arizona, Nevada, California, Nationwide, Worldwide

This website is owned by Computerized Business Associates. Computerized Business Associates is independent from Sage and is not authorized to make any statement, representation, or warranties or grant any license or permission on behalf of Sage regarding any product, service, or website content. Certain materials made available on or through this website are owned by Sage and cannot be used without the prior written permission of Sage.
© 2023 Computerized Business all rights reserved.