Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Excel 2010 VBA - How to delete excess spaces inside of a cell

I have a group of cells (A1:A300) that contain names.  However, immediately preceeding the names are a bunch of spaces.  I don't know how they got in there but I would like to have some way of deleting the spaces and retaining the names.
How can I do this using VBA?
Or maybe I don't need vba?
0
brothertruffle880
Asked:
brothertruffle880
1 Solution
 
byundtCommented:
Try using a formula like:
=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

The ASCII 160 non-breaking space is frequently found in data imported from elsewhere and cannot be removed by TRIM. That's why the SUBSTITUTE function is wrapped inside the formula--to convert those ASCII 160 non-breaking spaces to regular ASCII 32 spaces.

TRIM removes all leading and trailing ASCII 32 spaces, and all but one space between words.
0
 
brothertruffle880Author Commented:
Perfect.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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