find date string and paste into different column, same row

[rdowney79]

I have a report where I would like to have a macro search for a date string in columns N-S and copy the date to Column C, same row. The number of rows will vary from day to day. I would then like to format all of Column C to 'mm/dd/yyyy'.

Attached is a file where the date string is included in Column N on row 2 and Column O on the row 3. The dates for both these lines would need to be copied and pasted to their respective rows in Column C.

Thanks in advance
Ray

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[PCI]

Can we assume that each time there is date there the string "BY " and after some digits to remove?

[rdowney79]

PCI - Unfortunately no, the string 'BY' is not consistent across all rows and columns. Nothing about these fields is consistent. The only thing consistent about the date string are the hyphens '-'

[PCI]

How do you see there is a date in the cell

[FDibbins]

A fvew questions.

1. Does it have to be VBA, or will a formula do
2. What are the different formats of the dates? Do they always usae "-" or are there some with "/" or "."?
3. Will there be other instances, apart from the date, when "-" would be used?
4. Could more than 1 of the cells have a date in them, how would you want that handled?

[rdowney79]

A few questions.

1. Does it have to be VBA, or will a formula do
I would prefer VBA, but as long as I can get a macro to do the work, Im down to try it.
2. What are the different formats of the dates? Do they always usae "-" or are there some with "/" or "."?
The date string will always use a hyphen -, as displayed in the attachment.
3. Will there be other instances, apart from the date, when "-" would be used?
No, only the date string will contain a hyphen, no other text in these columns will use a hyphen
4. Could more than 1 of the cells have a date in them, how would you want that handled? No, the date and subsequent hyphens will only appear once in Columns N-S, however we could throw a simple error if so but its not necessary. Typically the date string will appear in Column N and the other columns are used for overspill, meaning if the string text doesnt all fit into column N, it will overspill into column O. If the string text doesnt fit into Column N and Column O, it will spill over to Column P and so on and so forth.

I hope this helps

[Leith Ross]

Hello rdowney79,

This macro will adjust to the number of rows with data automatically. It will then look through columns "N:O" and extract any dates from the text. The extracted dates are then copied into column "C".
Copy the code below and paste it into your "Moudle1".

Please Login or Register  to view this content.

[rdowney79]

Leith, how do I update this code to only copy/paste the date if the value in column C is Null

[Leith Ross]

Hello rdowney79,

Will there ever be two dates in columns "N:O" of the same row?

[rdowney79]

Hello rdowney79,

Will there ever be two dates in columns "N:O" of the same row?

There will always only be one date in columns N:O, actually we will want to look in columns N:S. But there will still always only be one date.

Thanks,
Ray

[FDibbins]

Try this regular formula, copied down in C...
=IFERROR(DATEVALUE(MID(OFFSET(N2,0,MATCH("*"&"-"&"*",N2:S2,0)-1),SEARCH("-",OFFSET(N2,0,MATCH("*"&"-"&"*",N2:S2,0)-1),1)-2,8)),"")

[Leith Ross]

Hello rdowney79,

Here is the updated macro. The macro has been added to the attached workbook.

Please Login or Register  to view this content.

[rdowney79]

Problem Solved. Thank you Leith!

[Leith Ross]

Hello rdowney79,

You're welcome.