Removing Spaces In Cells

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
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
 
Hi Chris,
Check out the TRIM function in the Help. It does exactly what you want, and
is easy to use.
Regards - Dave
 
cheers,

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

Regrads,

Chris
 
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
 
And you can incorporate the PROPER function like so:

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

Hope this helps.

Pete
 
Back
Top