i have had a 'database' created in excel ie rows + columns of data and lots of it !
one particular column of data has contents akin to : " 01.12.2010 city,state country " some of the cells are just the ' 01.12.2010 city, country' element only
i would like to split the date element from the location element - into 2 separate fields. and to then reformat the date into a usable field [ aim of the exercise is to be able to use the date portion ] the reformat of the date - i now know to do that bit
many thanks
Replies (5)
Please login or register to join the discussion.
ASSUMING that the date is seperated from the rest by a space (al
In row 2 of a new column if your data starts at A2, A1 being headings,
=LEFT(A2,SEARCH(" ",A2)-1).
PS Then copy it down all the rows
Data: Text to Columns
Assuming that the data always appears in the order you indicated, you could use "Text to Columns" from the Data menu.
You could use "fixed width" if the date is always the same length or you could use "delimited" using a space as the delimiter.
To Richard and Stevie
Once the 'date' bit is separated in the manner you have explained, would Excel not treat it as a text and hence incapable of being sorted in chronological order ? From experience, whenever a date is treated as a text (often after exporting into Excel) then any sorting is done by reference to the digits from the left - i.e. all the '1's together, then all the '2's, etc. Do you agree ?
It probably will if the seperators are fullstops
but you can get around that by selecting the column with the dates in and use Ctrl+H (Find & Replace) to replace . (fullstop) with / (slash)
Try ...
=SUBSTITUTE(LEFT(A1,SEARCH(" ",A1)-1),".","/") - this should equal 01/12/2010
and if you want to check the result is a date use =DATEVALUE(A2)