How do I remove spaces at the beginning of cells?

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.
 
T

T. Valko

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.
 
D

Dave O

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
 
B

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top