Replacing only the first three values in a string

CharlesB1611

New Member
Joined
Sep 8, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello,
Probably an easy one but I am somewhat new to vba and have this special project: I have a three column sheet that contains various strings in each cell of column C. Some of the columns have a date value of 09/04/2022 at the very beginning of the string, and some have a date field at the end of the string similar to "Birthdate: 04/16/1982" (quotes not included). I have a macro that goes through each line and replaces 09/ with DATE 09/. I only want it to change the date that is at the beginning not any other. My code is simple but finds the birthdate date and changes it as well. Any ideas? Here is my code:
Sub Clean_EJ01()

Worksheets("ConvertedEJ").Activate

With ActiveSheet

'Replace or remove new values with old values
Worksheets("ConvertedEJ").Columns("C").Replace What:="]", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="[PLU# : ", Replacement:="PLU#", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="01/", Replacement:="DATE 01/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="02/", Replacement:="DATE 02/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="03/", Replacement:="DATE 03/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="04/", Replacement:="DATE 04/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="05/", Replacement:="DATE 05/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="06/", Replacement:="DATE 06/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="07/", Replacement:="DATE 07/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="08/", Replacement:="DATE 08/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="09/", Replacement:="DATE 09/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="10/", Replacement:="DATE 10/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="11/", Replacement:="DATE 11/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="12/", Replacement:="DATE 12/", SearchOrder:=xlByColumns, MatchCase:=False

End With

End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Assuming that column C is text ie. your dates are actually text that look like dates ...

You could replace:
VBA Code:
Worksheets("ConvertedEJ").Columns("C").Replace What:="01/", Replacement:="DATE 01/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="02/", Replacement:="DATE 02/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="03/", Replacement:="DATE 03/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="04/", Replacement:="DATE 04/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="05/", Replacement:="DATE 05/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="06/", Replacement:="DATE 06/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="07/", Replacement:="DATE 07/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="08/", Replacement:="DATE 08/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="09/", Replacement:="DATE 09/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="10/", Replacement:="DATE 10/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="11/", Replacement:="DATE 11/", SearchOrder:=xlByColumns, MatchCase:=False
Worksheets("ConvertedEJ").Columns("C").Replace What:="12/", Replacement:="DATE 12/", SearchOrder:=xlByColumns, MatchCase:=False

with:
VBA Code:
    With Worksheets("ConvertedEJ")
        With .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
            .Value = Evaluate("IF(Mid(TRIM(" & .Address & "), 3, 1) =""/"",""Date "" & TRIM(" & .Address & ")," & .Address & ")")
        End With
    End With
 
Upvote 0
Solution

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top