If the cell value begins with a specific set of numbers, replace the data

Question

My cell values ​​are strings of numbers (there are always more than 5 numbers in a cell, i.e. 67391853214, etc.)

If a cell starts with three specific numbers (ie 673 in cell value 67391853214), I want the data in the cell to be replaced with another value (if 673 are the first numbers, replace the whole cell value with “790”)

I know there is a way to use an asterisk to use only part of the cell value, but I'm not 100% of the syntax. This is the current code that I have, but it is looking for exactly "### *", and not values ​​starting with "###". Any help is appreciated!

lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If c.Value = "614*" _ Or c.Value = "626*" _ Or c.Value = "618*" _ Or c.Value = "609*" _ Or c.Value = "605*" Then c.Value = "737" 

`

Answer 1

Use the LEFT() function as shown below:

 lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then c.Value = "737" 

Answer 2

It’s better to make a range, instead of looping through each cell for speed:

 Dim rng1 As Range Dim LastRow As Long Dim ColNum As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row On Error Resume Next ColNum = Application.Match("Number", Range("A1:CC1"), 0) On Error GoTo 0 If Column Is Nothing Then Exit Sub Set rng1 = Range(Cells(2, ColNum), Cells(LastRow, ColNum)) With rng1 .Replace "626*", "727", xlWhole .Replace "618*", "727", xlWhole .Replace "609*", "727", xlWhole .Replace "737*", "727", xlWhole End With 

Answer 3

Here is my problem:

 Sub SO() Dim MyString As String MyString = "614,626,618,609,605" For X = 1 To Range("C" & Rows.Count).End(xlUp).Row If Replace(MyString, Left(Range("C" & X).Value, 3), "") <> MyString Then Range("C" & X).Value = "737" Next End Sub