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