=LEFT - Cannot Format

[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!

[Dave Peterson]

This worked ok for me:

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



Mirish26 wrote:
>
> 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!
>
> --
> Mirish26
> ------------------------------------------------------------------------
> Mirish26's Profile: http://www.excelforum.com/member.php...o&userid=36966
> View this thread: http://www.excelforum.com/showthread...hreadid=566880

--

Dave Peterson

[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!

[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!!