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.