Excel document query

Excel document query

Didn't find your answer?

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.

avatar
By Richard Willis
13th Dec 2010 10:34

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

 

Thanks (0)
avatar
By stevie
13th Dec 2010 13:10

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.

 

 

Thanks (0)
avatar
By hossy2
14th Dec 2010 16:10

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 ?

Thanks (0)
avatar
By ACDWebb
14th Dec 2010 16:33

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)

Thanks (0)
avatar
By User deleted
14th Dec 2010 16:33

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)

 

Thanks (0)