Removing Spaces In Cells

[Chris]

Hi All,

Just wondering if it is possible to remove any SPACES/GAPS after text in
cells?
As I recieve data daily somtimes has gaps at the end of the text.

This text gets copyed and pasted into another spredsheet where a script is
run, but if there are any gaps after the text, the script fails.

Any help would be great,

Regards,
Chris

 

[Peo Sjoblom]

If you do this on a regular basis it's probably better to use a macro


http://www.mvps.org/dmcritchie/excel/join.htm#trimall



how to install a macro



http://www.mvps.org/dmcritchie/excel/install.htm



now all you have to do is to select the imported range and run the macro



--


Regards,


Peo Sjoblom

 

[Chris]

Ok, I just found that =TRIM(A2) works, but i also would like =PROPER(A2) to
be used as well, so that it Corrects the Name to the proper way of (example)
Chris and also deletes any unwatted spaces at the end?

Regards,

Chris

 

[Dave]

Hi Chris,
Check out the TRIM function in the Help. It does exactly what you want, and
is easy to use.
Regards - Dave

 

[Mike H]

Have a look at the TRIM() function in help

Mike

 

[Chris]

cheers,

but i would also like to use =PROPPER in my cell, as well as the =TRIM
Is this possible?

Regrads,

Chris

 

[Pete_UK]

If those gaps are caused by the normal space character (character 32)
then the TRIM function will get rid of any leading, trailing and
multiple spaces:

=TRIM(A1)

However, those gaps might be caused by the non-breaking space
character (160), and the easiest way to get rid of them is to
highlight the column(s), then CTRL-H (or Edit | Replace) and:

Find what: Alt-0160
Replace with: leave blank
click Replace All.

where Alt-0160 means hold down the ALT key while typing 0160 from the
numeric keypad.

Alternatively, you could use the SUBSTITUTE function, like so:

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

If you might have both types of spaces, then you could do this in one
formula:

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

Copy the formula down the helper column for as far as you need, then
you can fix the values if you need to.

Hope this helps.

Pete

 

[Pete_UK]

And you can incorporate the PROPER function like so:

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

Hope this helps.

Pete

 

[Chris]

ahh thanks pete, ur a STAR!

Twice in one day!

Thanks.

 

[Pete_UK]

You're welcome, Chris - thanks for feeding back.

Pete