Extract a date from a string

[DavidNO]

Hi Members

I have no real idea where to start on this one, i have data in a column eg Column "B2" as 16/12/13 AA 09.30, i would like to be able to extract the date portion and put in cell "C2", and put the time portion in "D2". The only part that could change would be the middle text which could be 16/12/13 ANG 09.30 or a different three characters but no more than three.
I hope this makes sense and someone is able to help.

Many Thanks
David

[jemmers]

Try this:

=LEFT(CELL,8) and in a new cell =RIGHT(CELL,4) That should pull the date out into two separate cells

[AlKey]

In C2

=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",255)),255))

In D2

=TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",255)),255))

	B	C	D
1
2	16/12/13 AA 09.30	16/12/13	09.30
3
4

[FDibbins]

You are using the UK date format, so this should work for you. For the date...
=DATEVALUE(LEFT(B2,8))
andthen for the time...
=TIMEVALUE(RIGHT(SUBSTITUTE(B2,".",":"),4))

[newdoverman]

With the time in B2 enter this in C2 and format as dd:mm:yyyy

Please Login or Register  to view this content.

In D2 enter this and format as h:mm

Please Login or Register  to view this content.

[DavidNO]

Hi FDibbins
I followed your code the date value worked but the time comes up with an error yet the calc steps show the time value correctly but the cell shows #value!, I cannot see what is missing?

[FDibbins]

what does your time format look like?

Try entering just 9:20 in a cell and see what happens?

[DavidNO]

Thank you all for your replies they all work but I now need to work out how to repeat the process in a macro to loop down a column and change each one, I will have a go before asking for help.

David

[DavidNO]

Yes it works I mistook the : for a . can this code be put in a macro to extract the same data down a column of data with the same structure.

[FDibbins]

VBA isnt my strong side, but Im sure someone can put that together for you

An alternative would be to put those 2 formulas in their own cells, copy down as far as needed, and then copy/paste values over the formulas to just have the answer - you can then delete (or hide) the original column if needed

[Debraj Roy]

Try this..

Please Login or Register  to view this content.

I think.. you can able to Change Range part or Formula to Value, if required.
Else.. Please let us know..

[DavidNO]

Hi Debraj Roy
The first part of your code works, but the second part for the time just puts TEXT(TIMEVALUE(SUBSTITUTE(MID(F2,SEARCH("??.??",F2),5),".",":")),"hh:mm") in every cell down the column? any thoughts please.

[Debraj Roy]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[DavidNO]

Debraj Roy

I have attached a file with a before and after sheet hope this helps.

Cheers
David

[newdoverman]

Hope that this helps.

[DavidNO]

Thank you that does help but i am now pulling the data into a word document as a mailmerge and the data is in your format rather than the uk format can this be corrected with VBA to format the cells before extracting the data?

[Debraj Roy]

I have attached a file with a before and after sheet hope this helps.

Did you tried previous code.. !!
Wait.. before that.. Please change the cell format of Column B & C to General... or (Col B as Date, and Col C as time..

[DavidNO]

Thank you for your help everyone i have it working i had to format the cells as text for it to work correctly.