Turn String in to Date Excel-VBA

Question

I am trying to turn a string containing unwanted text, a date, and an unwanted time in to an entry that contains only the date.

This is a sample of what I am starting with:

"Started: Sunday, October 30, 2016 3:17:37 PM"

And from that I would like to get:

"October 30, 2016"

I have tried to search the string for a comma using this code:

Dim fulldate As String, commaposition As Integer

fulldate = Cells(1, 1).Value
commaposition = InStr(fulldate, ",")

Cells(1, 2).Value = Right(fulldate, commaposition)

This however returns:

"2016 3:17:37 PM"

as it seems to pick the wrong comma. I was hoping for the other comma, and then I could work on getting the time out, but alas I am stuck.

Maybe there is some basic function I am missing? I understand looping and how to manipulate the code to work with multiple sets once I can figure the base out.

Darrell H · Accepted Answer · 2017-07-19 20:13:08Z

1

You can also use Mid to get everything after the first comma.

Cells(1, 2).Value = Mid(Cells(1, 1), commaposition + 2)

answered Jul 19, 2017 at 20:13

Darrell H

1,8961 gold badge11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carl Over a year ago

Thank you very much for you help!

sourceCode · Accepted Answer · 2017-07-19 22:44:52Z

0

You can use the Split function to separate Strings via a delimiter.

Dim fulldate As String

fulldate = Cells(1,1).Value
Cells(1, 2).Value = Split(fulldate,",")(1) & ", " & Left(Trim(Split(fulldate,",")(2)), 4)

edited Jul 19, 2017 at 22:44

answered Jul 19, 2017 at 20:00

sourceCode

3384 silver badges20 bronze badges

1 Comment

Carl Over a year ago

This does exactly what I want. Thank you very much for your help.

Collectives™ on Stack Overflow

Turn String in to Date Excel-VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related