Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
REMOVING TRAILING SPACES
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
REMOVING TRAILING SPACES
=substitute(A1," ","") and fill down, then copy the results, paste special
values onto the original. Bob Umlas Excel MVP "Tris" wrote in message ps.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
REMOVING TRAILING SPACES
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. "Tris" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
REMOVING TRAILING SPACES
Hi Tris - are they all exactly the same lenght?
"Tris" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
REMOVING TRAILING SPACES
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Tris" wrote in message ps.com... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
REMOVING TRAILING SPACES
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 "Tris" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove trailing spaces from multiple columns in Excel | Excel Worksheet Functions | |||
Removing spaces from columns | Excel Worksheet Functions | |||
Removing spaces in cells with data in it | Excel Discussion (Misc queries) | |||
how do I remove empty spaces trailing a text string? | Excel Worksheet Functions | |||
Removing trailing spaces from cells ? | Excel Worksheet Functions |