Delete spaces
 

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

Delete spaces
 

Use the TRIM function:

=TRIM(A1)

It removes leading and trailing spaces
--
Kevin Backmann


"rexmann" wrote:

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

Delete spaces
 

On Fri, 7 Mar 2008 05:41:02 -0800, rexmann
wrote:

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

Delete spaces
 

On Fri, 07 Mar 2008 09:07:57 -0500, Ron Rosenfeld
wrote:

On Fri, 7 Mar 2008 05:41:02 -0800, rexmann
wrote:

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

Or (slightly shorter):

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

--ron

Delete spaces
 

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)

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