Copy First portion of text into next Column

Status
Not open for further replies.

allanTeh

New Member
Joined
Jun 29, 2006
Messages
43
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 !!!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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 ?
 
Upvote 0
Hi,

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

HTH
 
Upvote 0
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.
 
Upvote 0
"...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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,218,788
Messages
6,144,498
Members
450,549
Latest member
desperato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top