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

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" wrote in message
...
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

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

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



"Chris" wrote:

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

cheers,

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

Regrads,

Chris



"Chris" wrote:

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



"Chris" wrote:

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

Have a look at the TRIM() function in help

Mike

"Chris" wrote:

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

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



On May 29, 3:45*pm, Chris wrote:
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

And you can incorporate the PROPER function like so:

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

Hope this helps.

Pete

On May 29, 4:02*pm, Pete_UK wrote:
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

On May 29, 3:45*pm, Chris wrote:



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- Hide quoted text -

- Show quoted text -

ahh thanks pete, ur a STAR!

Twice in one day! :)

Thanks.

"Pete_UK" wrote:

And you can incorporate the PROPER function like so:

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

Hope this helps.

Pete

On May 29, 4:02 pm, Pete_UK wrote:
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

On May 29, 3:45 pm, Chris wrote:



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- Hide quoted text -

- Show quoted text -

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

Pete

On May 29, 4:19*pm, Chris wrote:
ahh thanks pete, ur a STAR!

Twice in one day! :)

Thanks.



"Pete_UK" wrote:
And you can incorporate the PROPER function like so:

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

Hope this helps.

Pete