Find zero

[ericb1988]

In Column A I have a list of six digit numbers. I would like to find numbers that has a zero in the second and third digit in those numbers, and put a 1 for those numbers in the next column. Thank you.

[SamT]

This Code goes in a Standard Module. It adds a UserDefined Function to Excel's fx Icon

Option Explicit

Public Function ZerosToOnes(source As Range)
Dim Second As Boolean
Dim Third As Boolean
   
   If Mid(CStr(source), 2, 1) = "0" Then Second = True
   If Mid(CStr(source), 3, 1) = "0" Then Third = True
 
   If Second And Third Then
     ZerosToOnes = CDbl(Replace(CStr(source), "0", "1", 1, 2, 1))
      Exit Function
   End If
ZerosToOnes = ""
End Function

To use, Assume that the number with zeros is in Column "A1", place this formula in "B1"

=ZerosToOnes(A1)

You can treat UDFs like any other Excel Function

Edited: I liked Pauls code below so I shortening my code. It still works like the original

Public Function ZerosToOnes(source As Range)
   If Mid(CStr(source), 2, 2) = "00" Then
         ZerosToOnes = CDbl(Replace(CStr(source), "0", "1", 1, 2, 1))
   'To leave a blank cell as you didn't specify
   Else: ZerosToOnes = ""
   'Or use To not leave a blank
   'Else: ZerosToOnes = source
   End If
End Function

[Paul_Hossler]

Not much error checking

Option Explicit

Sub ZerosToOnes()
    Dim r As Range, r1 As Range
    
    With ActiveSheet
        Set r = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With

    For Each r1 In r.Cells
        If Len(r1.Text) = 6 Then
            If Mid(r1.Text, 2, 2) = "00" Then
                r1.Offset(0,1).Value = Left(r1.Text, 1) & "11" & Right(r1.Text, 3)
            End If
        End If
    Next
End Sub

[ericb1988]

Thanks for your help. Code works great. I really like the shorter one.