We have a personnel roster that is kept in a database. Occasionally,
this roster is exported into Excel and sent out to members of the
department. In one column are dates that each person was employed. When
I try to sort the entire roster, it doesn't come up right because most
of the cells that contain the date have spaces in front of it.
Sometimes it's 1 or 2, sometime more, other times there are no spaces.

In order for me to sort the entire roster correctly, I'm having to
manually remove the spaces in front of the date, then sort the roster
according to date.
There are over 500 entries in this roster, is there a better way to
remove the spaces other than manually?

Thanx...


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=391784

Ltat412a:

In a blank column, type this formula (this example assumes that the first
date is in cell A2):

=trim(A2)

Then copy the formulat by double-clicking it down the 500 rows. You can
then copy this column "over itself" by selecting the column, copy and then
paste special values. You can then sort on that column.

"Ltat42a" wrote:


We have a personnel roster that is kept in a database. Occasionally,
this roster is exported into Excel and sent out to members of the
department. In one column are dates that each person was employed. When
I try to sort the entire roster, it doesn't come up right because most
of the cells that contain the date have spaces in front of it.
Sometimes it's 1 or 2, sometime more, other times there are no spaces.

In order for me to sort the entire roster correctly, I'm having to
manually remove the spaces in front of the date, then sort the roster
according to date.
There are over 500 entries in this roster, is there a better way to
remove the spaces other than manually?

Thanx...


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=391784

Check out the TRIM function. This will remove all spaces from a cell
(except single spaces between words)

Use a 'helper' column next to your data (insert if needed), use this
formula (copied down your range):

=TRIM(A1)

---- of course, adjust the range as needed to match your data's
location

You can then Hide or Delete the original column.

Does this work for you?

Bruce

tip: save your data before you attempt any altering process...just in
case...


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=391784

What I have done in the past is an edit replace. Highlight the column you
wish to edit. From the main menu click on Edit - replace. When the box
appears go to the replace tab. Next to "find what:" put one space. Next to
"replace with:" leave it blank. Then choose "Replace All". This should
remove all spaces.

"Ltat42a" wrote:


We have a personnel roster that is kept in a database. Occasionally,
this roster is exported into Excel and sent out to members of the
department. In one column are dates that each person was employed. When
I try to sort the entire roster, it doesn't come up right because most
of the cells that contain the date have spaces in front of it.
Sometimes it's 1 or 2, sometime more, other times there are no spaces.

In order for me to sort the entire roster correctly, I'm having to
manually remove the spaces in front of the date, then sort the roster
according to date.
There are over 500 entries in this roster, is there a better way to
remove the spaces other than manually?

Thanx...


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=391784

carrie Wrote:
What I have done in the past is an edit replace. Highlight the column
you
wish to edit. From the main menu click on Edit - replace. When the
box
appears go to the replace tab. Next to "find what:" put one space.
Next to
"replace with:" leave it blank. Then choose "Replace All". This
should
remove all spaces.

"Ltat42a" wrote:


We have a personnel roster that is kept in a database. Occasionally,
this roster is exported into Excel and sent out to members of the
department. In one column are dates that each person was employed.
When
I try to sort the entire roster, it doesn't come up right because
most
of the cells that contain the date have spaces in front of it.
Sometimes it's 1 or 2, sometime more, other times there are no
spaces.

In order for me to sort the entire roster correctly, I'm having to
manually remove the spaces in front of the date, then sort the
roster
according to date.
There are over 500 entries in this roster, is there a better way to
remove the spaces other than manually?

Thanx...


--
Ltat42a

------------------------------------------------------------------------
Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
View this thread:
http://www.excelforum.com/showthread...hreadid=391784




Thanks everybody for replying. I tried several times using the TRIM
function. I was having trouble with this because it would often return
the text equilvalent of the date, it would not show the date.

I did try the replace idea, it seemed to work the best and removed all
the spaces and left the dates in place - Thanx!


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=391784

You are using Text functions therefore the result will be text.
You can probably hit F2 then enter, if that fixes your date
then there are several ways of solving the problem to simulate
a reentry. Include selecting an empty cell, copying it Ctrl+C
then selecting the column or other cells to be reentered and
using Edit, paste special, add.

But you would find it a lot easier to use a macro to remove
the leading and trailing spaces, or what would appear to be
spaces and essentially reenter their values. See the
TRIMALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If you need it there is also a link pointing you to
Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ltat42a" wrote in message
...

carrie Wrote:
What I have done in the past is an edit replace. Highlight the column
you
wish to edit. From the main menu click on Edit - replace. When the
box
appears go to the replace tab. Next to "find what:" put one space.
Next to
"replace with:" leave it blank. Then choose "Replace All". This
should
remove all spaces.

"Ltat42a" wrote:


We have a personnel roster that is kept in a database. Occasionally,
this roster is exported into Excel and sent out to members of the
department. In one column are dates that each person was employed.
When
I try to sort the entire roster, it doesn't come up right because
most
of the cells that contain the date have spaces in front of it.
Sometimes it's 1 or 2, sometime more, other times there are no
spaces.

In order for me to sort the entire roster correctly, I'm having to
manually remove the spaces in front of the date, then sort the
roster
according to date.
There are over 500 entries in this roster, is there a better way to
remove the spaces other than manually?

Thanx...


--
Ltat42a

------------------------------------------------------------------------
Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
View this thread:
http://www.excelforum.com/showthread...hreadid=391784




Thanks everybody for replying. I tried several times using the TRIM
function. I was having trouble with this because it would often return
the text equilvalent of the date, it would not show the date.

I did try the replace idea, it seemed to work the best and removed all
the spaces and left the dates in place - Thanx!


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=391784

ASAP Utilities, a free Add-in, available at www.asap-utilities.com has a
feature that does this nicely.

Vaya con Dios,
Chuck, CABGx3



"Ltat42a" wrote in
message ...

We have a personnel roster that is kept in a database. Occasionally,
this roster is exported into Excel and sent out to members of the
department. In one column are dates that each person was employed. When
I try to sort the entire roster, it doesn't come up right because most
of the cells that contain the date have spaces in front of it.
Sometimes it's 1 or 2, sometime more, other times there are no spaces.

In order for me to sort the entire roster correctly, I'm having to
manually remove the spaces in front of the date, then sort the roster
according to date.
There are over 500 entries in this roster, is there a better way to
remove the spaces other than manually?

Thanx...


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=391784

Thanx again for the replies, as for using macros, I get a whole new
spreadsheet every so often, so, I'd have to create the macro just so I
can run it. For this, the find/replace would work just fine.


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=391784