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.

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.

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

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.