Excel Puzzle - extracting a date...

[andyoxon]

Using Excel, how would I extract or isolate the date (05-02-12) only from the following cell info fromat... e.g.  123456 05-02-12 10'00'00.img        

Can it be done using find or replace, or a formula?  Any ideas ?   BTW the number of characters before the date can vary.

[chris n]

Is there always a space before and after it?  Data > Text to Columns, select Delimited, select Space > Finish

[PaulF]

If the number of characters after is constant you can use LEFT(RIGHT(cellreference,24),8) which will return 05-02-12

You may need to wrap it with a DATE() as well to force Excel to read is as date rather than text

[andyoxon]

The xx-xx-xx date is always flanked by spaces, and the last xx'xx'xx.img is always constant. Characters length before date varies.

[PaulF]

The xx-xx-xx date is always flanked by spaces, and the last xx'xx'xx.img is always constant. Characters length before date varies.

Then you should be OK with LEFT(....

[andyoxon]

If the number of characters after is constant you can use LEFT(RIGHT(cellreference,24),8) which will return 05-02-12

You may need to wrap it with a DATE() as well to force Excel to read is as date rather than text

Thanks LEFT(RIGHT(cellreference,25),8)  seems to do the trick... 

edit.  just realised that depending on the length of the numbers of initial characters, this add more 'spaces' to the right side e.g... in (cellreference,24) 24 needs to vary.  I think - somethings not right anyway.

[Pingu]

If the number of characters after is constant you can use LEFT(RIGHT(cellreference,24),8) which will return 05-02-12

You may need to wrap it with a DATE() as well to force Excel to read is as date rather than text

Thanks LEFT(RIGHT(cellreference,25),8)  seems to do the trick...   

Surely MID() is the function to use:

Code: [Select]

=MID(cellreference,8,8)

[andyoxon]

If the number of characters after is constant you can use LEFT(RIGHT(cellreference,24),8) which will return 05-02-12

You may need to wrap it with a DATE() as well to force Excel to read is as date rather than text

Thanks LEFT(RIGHT(cellreference,25),8)  seems to do the trick...   

Surely MID() is the function to use:

Code: [Select]

=MID(cellreference,8,8)

Thanks - will try this.

[Feanor]

Surely MID() is the function to use:

Code: [Select]

=MID(cellreference,8,8)

The problem is the start position is not well-defined.

The number of characters before the date is variable.
But the number of characters after the date id fixed, so working back from the right-hand side is better.

[mattc]

Hence chris had the 'easy' answer:

Is there always a space before and after it?  Data > Text to Columns, select Delimited, select Space > Finish

Although andy might want to automate the process, so a formula would be nice.

(Dunno if excel has a "cut" formula     )

[PaulF]

If the number of characters after is constant you can use LEFT(RIGHT(cellreference,24),8) which will return 05-02-12

You may need to wrap it with a DATE() as well to force Excel to read is as date rather than text

Thanks LEFT(RIGHT(cellreference,25),8)  seems to do the trick... 

edit.  just realised that depending on the length of the numbers of initial characters, this add more 'spaces' to the right side e.g... in (cellreference,24) 24 needs to vary.  I think - somethings not right anyway.

You can use the LEN() function to return the number of characters. Can you then use that to establish what value to use instead of 24?

[Dibdib]

=DATE(2000+LEFT(RIGHT(C10,15),2),LEFT(RIGHT(C10,21),2),LEFT(RIGHT(C10,18),2))

it's a bit brute force but seems to work. assumes your format is MM-DD-YY (i'm guessing the example is today's date). Change the cell reference to wherever you want to, of course.

Or, if you want to be incredibly precise (this looks for the first hyphen and then works sideways from there)

=date(2000+LEFT(RIGHT(C10,LEN(C10)-FIND("-",C10)-3),2),LEFT(RIGHT(C10,LEN(C10)-FIND("-",C10)+3),2),LEFT(RIGHT(C10,LEN(C10)-FIND("-",C10)),2))

Yes, I need to get out more.

[andyoxon]

=DATE(2000+LEFT(RIGHT(C10,15),2),LEFT(RIGHT(C10,21),2),LEFT(RIGHT(C10,18),2))

it's a bit brute force but seems to work. assumes your format is MM-DD-YY (i'm guessing the example is today's date). Change the cell reference to wherever you want to, of course.

Or, if you want to be incredibly precise (this looks for the first hyphen and then works sideways from there)

=date(2000+LEFT(RIGHT(C10,LEN(C10)-FIND("-",C10)-3),2),LEFT(RIGHT(C10,LEN(C10)-FIND("-",C10)+3),2),LEFT(RIGHT(C10,LEN(C10)-FIND("-",C10)),2))

Yes, I need to get out more. 

Thanks - I tried your first expression (substituting C10 for the actual cell) and excel says "a value used in the formula is of the wrong date type"
(that's right 05-02-12 is today)

[TheLurker]

Or add a function to the worksheet's code file and use that. Something like this: 

Public Function Extract(OddFormat As String) As Date

   ' Break date part out of strings like  "123456 05-02-12 10'00'00.img"
   ' and return as a date.
   ' Assumes that datepart always delimited by single space and
   ' there are no leading or trailing spaces.  See TRIM() if there are.
   
    Dim Chunk() As String
    Dim DatePart As String
   
    Chunk = Split(OddFormat, " ")
    DatePart = Chunk(1)

    Extract = Conversion.CDate(DatePart)

End Function

[Ham]

Given what you said, the easiest way to get the date string out is  =MID(cellref,LEN(cellref)-21,9)

Bear in mind that this is still a string at this stage and needs to become a date if you want to manipulate

edit: I would use a second cell to turn it into a date which would read like =DATE(2001+RIGHT(newref,2),MID(newref,4,2),LEFT(newref,2)) where newref is the stripped string location

[iddu]

space, or spaces

=LEFT(TRIM(MID("RandomStringWithNoWhitespace         dd-MM-yy (and some more whitespace)   hh:mm:ss.img ",FIND(" ","RandomStringWithNoWhitespace         dd-MM-yy (and some more whitespace)   hh:mm:ss.img "),LEN("RandomStringWithNoWhitespace         dd-MM-yy (and some more whitespace)   hh:mm:ss.img "))),8)

will sort you, for spaces.

Other whitespace characters and/or "RandomStingWithNoWhitespace" having whitespace will need further coding...

[David Martin]

awk '{print $2}' doesn't quite cut it, does it?

Actually, excel's date handling has to be the most broken and steaming pile of excrement. Any program that decides your data looks like a date and will then change the underlying value to fit it's model of what it thinks it is rather than altering it's representation of what you put in, is fundamentally broken.
Dates and excel are unhappy bedfellows and best avoided.
 

[TheLurker]

awk '{print $2}' doesn't quite cut it, does it?

Actually, excel's date handling has to be the most broken and steaming pile of excrement.  <snip />

[andyoxon]

Thanks peeps.

I've extracted the dates into another column, but excel won't see the cell contents with format MM-DD-YY e.g. 04-27-11 as a date...  Any more ideas?

[hulver]

Replace the "-" with a "/" ?

[Ham]

edit: No, stoopid me you've just extracted text  (a) it should do, really. Check your date format options. Actually, try typing in 14-05-12 and see if it converts to a date. Next will work:

(b) Do it manually. In another cell enter the function: DATEVALUE( {cell with date looking text} ) - should get you a number. Format that cell as "Date" and your work is done

edit: step b¹is to wrap the datevalue function around the formula that extracts the text, or just leave in two cells

[tiermat]

awk '{print $2}' doesn't quite cut it, does it?

Nor 'cut -d " " -f2'

One day people will realise....

[David Martin]

insert into foo (mydate) values (todate( dateval, 'MM-DD-YY')) might also do the trick depending on how you store the values.

[andyoxon]

ham - sorry couldn't get to work.
DM - couldn't work out what to do with your suggestion - being dim.

I managed to convert MM-DD-YY to MM-DD-YYYY using English (US) date:
something like...   Select>format>English US date> yellow triangle warning> right click > convert to 20XX option selected.
but unfortunately it's still not being recognised as something I can convert to a UK date format...

[Dibdib]

Be careful with that - Excel be stoopid, and given a list of mm/dd/yyyy dates will sometimes quietly treat the ones which can be parsed as dd/mm/yyyy and only throw an error for the others.

e.g. given two dates in MM/DD/YY such as "1/31/12" and "2/1/12", it'll give an error for the first but will happily accept the second... as the 2nd of Jan.