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)

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

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

HTH
 

allanTeh

New Member
Joined
Jun 29, 2006
Messages
43

ADVERTISEMENT

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.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"...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.
 
Status
Not open for further replies.

Forum statistics

Threads
1,141,842
Messages
5,708,919
Members
421,598
Latest member
NewHere

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
Top