Extracting Dates and Times from cell.....

[angieg]

Hello!
I need help extracting dates/times out of a cell that looks something like this:

E:RECEIVER REFUSED, CANCELLED ORDER;PACKAGE RETURNED TO SENDER04/15@16:30RECEIVER REFUSED, CANCELLED ORDER04/15@10:07ROUTED INCORRECTLY AT UPS FACILITY;PKG HAS BEEN REROUTED TO DESTINATION04/13@7:48

The dates and times will not be in the same place everytime, and I can't figure out how to pull the dates/times out. Perfect solution would be to pull out the dates and times and put them in adjacent cell. Can anyone help? Would be grateful.

Thanks in advance,
Angie

[bhofsetz]

Which date / time do you need? There are three in your example.
An idea would be that you could do an InStr search for the @ and extract the 5 characters before and the 5 charachters after in to two different variables and them copy those into the adjacent cells on your output worksheet.

Just a quick bit of code that works for your example.

Sub Date_Time()

Pos = InStr(1, Range("A1"), "@")
Date1 = Mid$(Range("A1"), Pos - 5, 5)
Time1 = Mid$(Range("A1"), Pos + 1, 5)

Pos2 = InStr(Pos + 1, Range("A1"), "@")
Date2 = Mid$(Range("A1"), Pos2 - 5, 5)
Time2 = Mid$(Range("A1"), Pos2 + 1, 5)

Pos3 = InStr(Pos2 + 1, Range("A1"), "@")
Date3 = Mid$(Range("A1"), Pos3 - 5, 5)
Time3 = Mid$(Range("A1"), Pos3 + 1, 5)

MsgBox "Here are the three dates / times" & vbCr & vbCr & _
"1: " & Date1 & " / " & Time1 & vbCr & _
"2: " & Date2 & " / " & Time2 & vbCr & _
"3: " & Date3 & " / " & Time3

End Sub

You should refine it to include delaring your variables, set up with a loop, etc.
One problem I forsee is if the date or time is less than 5 characters then it will pull the preceeding or following letter. But you can check if the first character in the Date or last character in the time is a number (with IsNumeric) and truncate accordingly.

Hope this helps.

B

[angieg]

B,
What if there only two dates/times or what if there were five dates/times? And how do I get it to loop to the next cell. And instead of a message box, can it put the response in cell B1? Thanks for your help!
Angie

[bhofsetz]

Angie,
Where is the original cell located? Column A always? and how many rows of data will there be (vairble I'd guess)?
Do you really want the output in column B or would it be better on a new sheet?
And how do you want it to handle multiple Dates/Times found in each original cell?

i.e.
If the string in A1 has 3 dates and times do you want them output to

Date1 in B1
Time1 in C1
Date2 in D1
Time2 in E1
Date3 in F1
Time3 in G1

Then if the string in A2 has 5 dates and times similar output all the way to column K?
Should the first date/time always end up in the same output column or is there some qualifier in the text which they could be used to group them for output?

[angieg]

Actually the data is always in column AC. The number of rows will be variable. The example of the output you gave was exactly what I want. Is this doable?
Thanks for all your help. Sorry for being such a beginner.
Angie

[bhofsetz]

Angie,
Where do you want your output to go and are there any column headers for each Date/Time combination?

[angieg]

I would like the output to go to AD,AE,AF etc...No, there aren't any column headers.
Angie

[bhofsetz]

Give this a try and let me know how it works.

Please Login or Register  to view this content.

HTH

B

[bhofsetz]

I just found an error

In the first line of the sub

RowCount = UsedRange.Rows.Count

change this to

RowCount = ActiveSheet.UsedRange.Rows.Count

sorry for the error

B

[angieg]

Thanks B!! You are heavensent. The coding worked just the way we needed it to. We found the error on the first line and tweaked it a little, but other than that, it was perfect. Thanks for all of your help!!!
Angie