Excel VBA - Replace Text in Table Column

Question

I have a macro which pastes dates as text in a column and I have read somewhere, the best solution to convert the text back to dates is to do a search and replace of "/".

If I do this manually in Excel, it works like a breeze. However, when I try to do this with a macro, I do not see the cells being converted to dates.

This is what the macro recorder gave me and what I am trying to use:

Range("t_drill[Date]").Replace What:="/", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

"somewhere" is wrong, your dates may be separated by dots. And this idea builds on a side effect, not very nice. — z32a7ul
– z32a7ul, Commented Jun 3, 2019 at 11:51
Try to set the Value property of each Cell to CDate() of something. That will cause date formatting. — z32a7ul
– z32a7ul, Commented Jun 3, 2019 at 11:53
There are no dots, the dates are always separated with "/" and if I manually run search and replace, I can achieve the required result — JoaMika
– JoaMika, Commented Jun 3, 2019 at 11:55
Also, if I manually enter each cell and press "Enter" the text is automatically converted to date. — JoaMika
– JoaMika, Commented Jun 3, 2019 at 11:58

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Using TextToColumns to convert date:

This is for the date format DMY, if you use a different date format then you will have to make adjustments.

Range("t_drill[Date]").TextToColumns Destination:=Range("t_drill[Date]"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True

To adjust for different date formats change as required:

MDY :=Array(1, 3)

DMY :=Array(1, 4)

YMD :=Array(1, 5)

MYD :=Array(1, 6)

DYM :=Array(1, 7)

YDM :=Array(1, 8)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 3, 2019 at 12:00

5202456

97214 silver badges25 bronze badges

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

Comments

z32a7ul · Accepted Answer · 2019-06-03 13:42:24Z

0

Try setting the Value to the return value of CDate. That changes the formatting of the cell, too.

Dim c As Variant: For Each c In Range("t_drill[Date]").Cells
    c.Value = CDate(c.Value)
Next c

Note: Originally I thought t_drill[Date] is a single cell. I updated my code so that it works for multiple cells.

edited Jun 3, 2019 at 13:42

answered Jun 3, 2019 at 11:55

z32a7ul

3,8574 gold badges24 silver badges49 bronze badges

1 Comment

L. F. Over a year ago

It is preferred that you add some explanation along with your code.

Collectives™ on Stack Overflow

Excel VBA - Replace Text in Table Column

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related