=LEFT - Cannot Format

M

Mirish26

I downloaded over 2,000 entries which contain date and time in one cell
(20060428 3+12+32). I need the date only and in the format: 04/28/06.
I used =LEFT to obtain the result 20060428. I then tried to format the
result as 04/28/06. Nothing works - I tried all format options and
Data-Text to Columns. I also tried =MID in a futile attempt to
rearrange the numbers!

I presume the problem is because even though the cell appears as:
20060428 it is actually =LEFT(M4,8), etc. - which cannot be formatted.
There was a tip on this site about using =LEFT and "ignore" but that
didn't work either (unless I wasn't using it correctly). Any ideas?
Since I have over 2,000 entries I am desperate! Thanks!
 
D

Dave Peterson

This worked ok for me:

=--TEXT(LEFT(A1,8),"0000\/00\/00")
format it as a date.
 
M

Mirish26

Thanks for your response. I entered it as you indicated (=Text) and the
result was: 2006/4/28, however, when I try to format it as a date
mm/dd/yyyy - nothing happens!
 
M

Mirish26

I didn't realize that I was supposed to enter the two -- after the =
sign. I just tried it and it worked. You are brilliant - never in a
million years would I have figured this out! Genius!!
 
Top