Copy First portion of text into next Column

[allanTeh]

I have a Column (let's say Column 'A') that store data such as:-

A1 : Aug 04-19:30
A2 : Jul 30-19:30
A3 : Aug 05-17:00
etc...


I want to store data in Column 'B' the DATE portion (the FIRST portion BEFORE the minus sign in Column 'A').


PROBLEM :

How to make Column 'B' store REAL DATEs that can be SORTED ?

Pls HELP !!!

 

[venkat1926]

one way of doing it

provided the format is the same i.e mmm three letters for month and two numbers for date with space in between then type in B1
=left(a1,6)

copy B1 down.

 

[allanTeh]

Hi Venkat1926,
I can get the dates, BUT it cannot be sorted correctly from earliest date to later dates, but it was sorted as alphebetical order (month Aug come first, then Jul).

Any other solution ?

 

[Krishnakumar]

Hi,

Try Data > Text to columns.. check Delimited > Next > check other and type "-" in the box without quotes >next > Finish.

HTH

 

[allanTeh]

Any Other Better Method

Hi KrishnaKumar,
The method you given worked fine, but besides getting the DATE portion in Column 'B' (that's what I needed), the method also get the TIME portion into Column 'C' (that's what I don't need). That means my other existing data in Column 'C' being overwritten by that method.

So, I hope to have another method where I type in a formula/function in Column 'B'.

Regardless, thanks for your help.

 

[Krishnakumar]

Hi,

Try,

=LEFT(A1,6)+0

format the cell

HTH

 

[PaddyD]

"...That means my other existing data in Column 'C' being overwritten by that method. "

review the furthe options on the data | text to columns wizard - you can choose whether or not to import individual columns & whether to overwrite existing data.

 

[Joe4]

Duplicate:
http://www.mrexcel.com/board2/viewtopic.php?t=231357&highlight=