Remove leading or trailing spaces in an entire column of data

by

Quite often the issue is a non-breaking space – CHAR(160) – especially from Web text sources -that CLEAN can’t remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Ron de Bruin has an excellent post on tips for cleaning data here

You can also remove the CHAR(160) directly without a workaround formula by

  • Edit …. Replace your selected data,
  • in Find What hold ALT and type 0160 using the numeric keypad
  • Leave Replace With as blank and select Replace All

More Related Contents:

  1. Ignore Duplicates and Create New List of Unique Values in Excel
  2. Excel: Searching for multiple terms in a cell
  3. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  4. How to convert to percentage [closed]
  5. Excel Number Separation [closed]
  6. I have an Excel data sheet that I would like to extract data from into a new sheet
  7. Getting unique values in Excel by using formulas only
  8. Excel: last character/string match in a string
  9. Referencing value in a closed Excel workbook using INDIRECT?
  10. Simple Pivot Table to Count Unique Values
  11. Excel UNIQUE Across Columns
  12. Filling any empty cells with the value above
  13. How to create a string or formula containing double quotes in Excel?
  14. Find last used cell in Excel VBA
  15. Count Unique values with a condition
  16. Excel Date to String conversion
  17. Excel: Formulas for converting data among column / row / matrix
  18. Three Dimensional Lookup Using INDEX/MATCH
  19. Cannot VBA write data to cells in Excel 2007/2010 within a function
  20. How to list all possible combinations of the values in three columns in excel?
  21. How to split texts from dynamic range?
  22. Excel: the Incredible Shrinking and Expanding Controls [closed]
  23. Excel: Search for a list of strings within a particular string using array formulas?
  24. Convert from US to UK format
  25. excel vlookup with multiple results
  26. Date-Formatting in TEXT formula independent from used language in Excel
  27. Check whether a cell contains a substring
  28. Unique Rank value for a subgroup within a group
  29. Get content of a cell given the row and column numbers
  30. How does one restore default case to a variable in VBA (Excel 2010)?

Leave a Comment