How do I remove spaces at the beginning of cells?

  • Thread starter Thread starter Munkeeric
  • Start date Start date
M

Munkeeric

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.
 
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 click>Copy
Right click>Paste Special>Values>OK

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.
 
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
 
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
 
Back
Top