Delete spaces

[rexmann]

Hi All

Is there a formula that deletes all the spaces at the end of a cell of text
but not the spaces in the middle of the cell?

Cheers Rexmann

PS Excel 2003

 

[Kevin B]

Use the TRIM function:

=TRIM(A1)

It removes leading and trailing spaces

 

[Ron Rosenfeld]

Hi All

Is there a formula that deletes all the spaces at the end of a cell of text
but not the spaces in the middle of the cell?

Cheers Rexmann

PS Excel 2003

The TRIM worksheet function will delete spaces at the beginning and end, and
also leave only a single space between words within the text string.

To delete ONLY the spaces at the end, you can try this formula:

=LEFT(A1,LEN(A1)-SUMPRODUCT(--(TRIM(MID(A1,1+
LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),255))="")))

--ron

 

[Ron Rosenfeld]

The TRIM worksheet function will delete spaces at the beginning and end, and
also leave only a single space between words within the text string.

To delete ONLY the spaces at the end, you can try this formula:

=LEFT(A1,LEN(A1)-SUMPRODUCT(--(TRIM(MID(A1,1+
LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),255))="")))

--ron

Or (slightly shorter):

=LEFT(A1,LEN(A1)+SUMPRODUCT(-(TRIM(MID(A1,1+
LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),255))="")))

--ron

 

[Ron Coderre]

Maybe one of these:

For all text lengths:
=LEFT(A1,COUNT(INDEX(1/(TRIM(RIGHT(A1,ROW(INDEX($A:$A,1):
INDEX($A:$A,LEN(A1)))))<>""),0)))

or...if the text length will be 255 chars or less:
=LEFT(A1,COUNT(INDEX(1/(TRIM(RIGHT(A1,COLUMN($1:$65536)))<>""),0)))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)