• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1002
  • Last Modified:

how to remove all spaces in an EXCEL column

How can I remove all spaces (trailing, leading,...) for a EXCEL spreadsheet coloumn?
0
Hojoformo
Asked:
Hojoformo
  • 3
1 Solution
 
Patrick MatthewsCommented:
Hello Hojoformo,

Here is a really simple way:

1) Select your column(s)

2) Hit Ctrl+H to get the find/replace dialog

3) In the find, type a single space.  In the replace box, make sure it is totally empty

4) Click Replace All

Regards,

Patrick
0
 
Calvin BrineCommented:
1.  Insert a column beside your column you need to remove spaces from.
2.  Enter this formula and copy it down to the bottom of the column "=Trim(B2)", where B2=Your text and spaces.
3.  Copy the new column and pastespecial Values over the original column.
4.  Delete the column with the formula.
HTH
Cal
0
 
Patrick MatthewsCommented:
Hojoformo,

Please note that me suggestion replaces ALL spaces.  Cal's removes leading and trailing spaces, and any
internal spaces of 2+ are scaled back to a single space.  Only you know which of those is what you really
wanted :)

Regards,

Patrick
0
 
Patrick MatthewsCommented:
Hojoformo,

BTW, you should delete that duplicate question you opened...

Regards,

Patrick
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now