Menu
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.)
If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are the first numbers, replace entire cell value with "790")
I know there's a way to use an asterick to use only part of the cell value but I'm not 100% on the syntax. This is the current code I have, but it's searching for specifically "###*", not values that start with "###". Any and all help is greatly 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"
`
814
The SUBSTITUTE function replaces text from a text string with an instance number. Here, we will apply this function to replace the text of a cell with a condition. We again will consider the condition mentioned in the previous method. We will replace the data of Cell B9. Go to Cell E9. Apply the SUBSTITUTE function here. Write the formula below:
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the d... Stack Overflow About Products For Teams Stack OverflowPublic questions & answers
Use REPLACE Function with a Condition in a Cell The REPLACE function is used to supersede a certain number of letters from a word and form a new word in Excel. In this section, we will discuss how this REPLACE function with condition. We assume that some of our data in the dataset are wrong. We will correct that data using the Replace function.
Check if cell begins or ends with a specific character with Kutools for Excel. Check if cell begins or ends with a specific character easily with Kutools for Excel: With the Select Specific Cells utility of Kutools for Excel, you can easily check and select cells if it begins or ends with a specific character in a specific range.
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"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
