REMOVING TRAILING SPACES

[Tris]

Can anyone please help!!!!

I have excel files supplied to me

as they currently stand they are as

*12345678 *
*90123456 *

I need to get read of the space between the last number and the last
asterix

I have tried and failed for hours!!

Any one know the formula i can use without going through and deleting
each of the spaces by hand. I have over 10000 to do so it will take a
while.

Many Thanks

Tris

 

[Bob Umlas]

=substitute(A1," ","") and fill down, then copy the results, paste special
values onto the original.
Bob Umlas
Excel MVP

 

[Guest]

use the find/replace feature to replace all spaces with nothing

1. Select Edit->Find
2. Click Replace
3. Enter a space in the "find" field and nothing in the replace field.
4. Click replace all.

 

[Guest]

Hi Tris - are they all exactly the same lenght?

 

[Bernard Liengme]

These are not true space characters (ASCII 32) but HTML fixed-space (ASCII
160)

Assuming your values are in column A
Insert a new B column
In B1 enter =SUBSTITUTE(A1,CHAR(160),"")
Copy down the column
Now select all the B cells, use Copy, then Edit|Paste Special and specify
Values
B now has just what you want so you can delete column A and let B take its
place

Alternatively use the macro at:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

best wishes

 

[Guest]

Hi Tris - if they are not the same length but that there is always a space
before the last *

Assuming the number is in cell B10 put this in cell C10
=LEFT(B10,LEN(B10)-2)

In Cell D10 put =CONCATENATE(C10,"*") if you want the * back on again.

Copy these formula down the whole length of the 1000 cells

THEN - select and copy the cells in column D - put your cursor where you
want the new cells to be and right click - then select Paste Special and
choose Value.

Hope this helps --- Yours Dika