Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove spaces at the beginning of cells?
I have a column of names I need to sort. Unfortunately, some of the cells
have 1 or 2 leading spaces, which cause the zero-leading spaces to be sorted, then the one-leading space, then two-leading space. And since there are over 3000 names to be sorted, doing them by hand is problematic. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove spaces at the beginning of cells?
One way...
Use a temporary helper column and enter a formula like this: =TRIM(A1) Copy that formula down as far as needed. Then, select the entire range of formulas and do: Right clickCopy Right clickPaste SpecialValuesOK Compare the 2 columns and make sure the helper column removed the spaces (they may not be standaed char 32 spaces). If everything checks out you can then replace the original column of names with the space-removed column of names. -- Biff Microsoft Excel MVP "Munkeeric" wrote in message ... I have a column of names I need to sort. Unfortunately, some of the cells have 1 or 2 leading spaces, which cause the zero-leading spaces to be sorted, then the one-leading space, then two-leading space. And since there are over 3000 names to be sorted, doing them by hand is problematic. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove spaces at the beginning of cells?
Try the TRIM() function, which removes leading and trailing spaces.
The syntax is =TRIM(cell reference). Step 1: make a backup copy of your file to avoid losing data! If you insert a column and copy the TRIM function for all cells in the column, you can paste the results as values over the original column, then delete the column you added. Dave O Eschew obfuscation |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I remove spaces at the beginning of cells?
If A1 has the value " apple" -- without the quotes of course
In B1 I can enter the formula =TRIM(A1) to get "apple" - no quotes If column A has lots of such text, I could double click B1's fill handle (solid square in lower right corner of B1 when I make it the active cell) to fill down the bottom of the column. If column B is already in use I could insert a new blank column (soon to be removed) Now I will select all the B entries and use Copy, then with them still selected I will se Edit | paste Special Values to convert the formulas to values. Now I can delete column A since my names are now all tidied up best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Munkeeric" wrote in message ... I have a column of names I need to sort. Unfortunately, some of the cells have 1 or 2 leading spaces, which cause the zero-leading spaces to be sorted, then the one-leading space, then two-leading space. And since there are over 3000 names to be sorted, doing them by hand is problematic. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you remove leading spaces etc in cells? | New Users to Excel | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
How can I insert 2 spaces at the beginning of each of 1300 cells? | Excel Discussion (Misc queries) | |||
Remove Spaces beginning Time entry | Excel Discussion (Misc queries) | |||
how do I remove spaces from cells that were pasted | Excel Discussion (Misc queries) |