In cell A2, I use the trim function to trim cell A1 containing a date eg
1202/09, the result in A2 is 39856. However, this 39856 is text and not date
value. I therefore cannot convert it back to date after the trimming. Can
someone please help? Many thanks...
--
exalan

Just format the cell as date to display:
2/12/2009

--
Gary''s Student - gsnu200833

On Feb 14, 7:17*am, exalan wrote:
In cell A2, I use the trim function to trim cell
A1 containing a date eg 1202/09, the result in A2
is 39856. However, this 39856 is text and not date
value. I therefore cannot convert it back to date
after the trimming. Can someone please help?

=value(trim(A1))

should do the trick. You still might need to explicitly format the
cell as Date. (But it worked for me without that when I did that in a
virgin cell.)

Try
=--TRIM(A1)


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64437

Million thanks to Gary"s Student, joeu2004 and mdmackilop for their prompt
response. I've got it!
--
exalan


"mdmackillop" wrote:


Try
=--TRIM(A1)


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64437

Hi,

I know your problem has been solved, but I do have two questions -
1. why are you entering dates 1202/09 (Excel will not treat these as dates)
it should be 12/02/09
2. what is the purpose of using TRIM? It is designed to remove leading and
trailing spaces plus any extra spaces in between words. If you have a date
in A1 then TRIM really does nothing.
3. If on the other hand 12/02/09 is text then rather than use =--TRIM(A1)
just use =--A1 and format the cell accordingly.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"exalan" wrote:

In cell A2, I use the trim function to trim cell A1 containing a date eg
1202/09, the result in A2 is 39856. However, this 39856 is text and not date
value. I therefore cannot convert it back to date after the trimming. Can
someone please help? Many thanks...
--
exalan

Hello Shana, responded yesterday but not sure why not yet posted, hence this
repeated reply below:

Yes, I've simplified the question because I know the result is the same.

My real case is: I've donwloaded some data from "Pro Alpha" ESR system and
done the "text to column" and "trim" routines. The date columns after "trim"
all become text and I need to make use of the dates to continue my data
analysis.

Anyway, your advice on no.3 is helpful to me. Thanks
--
exalan


"Shane Devenshire" wrote:

Hi,

I know your problem has been solved, but I do have two questions -
1. why are you entering dates 1202/09 (Excel will not treat these as dates)
it should be 12/02/09
2. what is the purpose of using TRIM? It is designed to remove leading and
trailing spaces plus any extra spaces in between words. If you have a date
in A1 then TRIM really does nothing.
3. If on the other hand 12/02/09 is text then rather than use =--TRIM(A1)
just use =--A1 and format the cell accordingly.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"exalan" wrote:

In cell A2, I use the trim function to trim cell A1 containing a date eg
1202/09, the result in A2 is 39856. However, this 39856 is text and not date
value. I therefore cannot convert it back to date after the trimming. Can
someone please help? Many thanks...
--
exalan