Trim function

[exalan]

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

 

[Gary''s Student]

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

 

[joeu2004]

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

 

[mdmackillop]

Try
=--TRIM(A1

 

[exalan]

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

 

[Shane Devenshire]

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.

 

[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