CODEWITHSUNDEEP
excelvba
Most Important Things
Most Important Things

Reputation: 97

How to Replace a Value in a String in Column with a Do Loop in VBA

I have a column of telephone numbers. Some of them contain two continuous hyphen "--" instead of one "-". I would like to replace the "--" with "-".
The problem is if the "--" appears only once for each cell, then that's fine, but it may have situations like "----" or "---", or even "-----------". My hunch is to use a Do While or Until Loop, but I don't know how. And I think I will need the Find Function, but I don't know how to combine them.
Can somebody kindly enough help me please?
Thanks.

Upvotes: 0

Views: 3300

Answers (6)

jeremyh
jeremyh

Reputation: 644

You could try a recursive function to do this for example:

function recursiveReplace(text as string, oldText as string, newText as string) as string
    dim result as string
    result = replace(text, oldText, newText)

    if instr(1, result, oldText) > 0 then
        recursiveReplace = recursiveReplace(result, oldText, newText)
    else
        recursiveReplace = result
    end if
end function

sub test()

    const phoneNumber as string = "234-45---34----112"
    debug.print recursiveReplace(phoneNumber, "--", "-")

end sub

You can now call this function directly in an excel worksheet too! For example =recursiveReplace("234---456-----67--5", "--", "-")

Upvotes: 0

Siva
Siva

Reputation: 1149

Sub CorrectTelephoneNo()

    Dim strHypens
    strHypens = "--"
    Dim strFound As Boolean
    strFound = True 'Assuming there is atleat on cell with "--"
    Do While (strFound = True)
        'Replace all the "--" with "-"
        Cells.Replace What:="--", Replacement:="-", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        'Check if there are any more numbers with "--"
        On Error GoTo x
            strFound = Cells.Find(What:="--", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
            GoTo y
x:          strFound = False

y:     Loop
    MsgBox "done"

End Sub

Upvotes: 0

iDevlop
iDevlop

Reputation: 25272

Incredibly complicated and unefficient answers I see !!!

Range("c3:c17").Replace "--", "-"
Range("c3:c17").Replace "--", "-"

should do the trick in 2 lines for up to 4 hyphens, and probably much faster than the complicated answers. Add a third line for handling up to 6 consecutive hyphens.

If the number of consecutive hyphens can be really large (not as in the OP) then the following loop might be better: 

With Range("c3:c17")
    Do Until .Find("--") Is Nothing
        .Replace "--", "-"
    Loop
End With

Don't forget that accessing the spreadsheet from VBA is an expensive operation. Using 1 replace for a range is way faster than doing it cell by cell. Of course this will only be visible if you handle large amounts of data.

Upvotes: 3

manu
manu

Reputation: 942

Try this

Sub rep()

For Each c In Sheets("Sheet1").Range("A1:A10").Cells ' Change the range that you want

    If InStr(c.Value, "--") > 0 Then
        deli = Split(c, "-")
        For a = 0 To UBound(deli)
        c.Value = Replace(c.Value, "--", "-")
        Next a

    End If

Next c

End Sub

Upvotes: 1

gadi
gadi

Reputation: 501

Try this : 

Dim my_switch As Boolean
my_switch = True
Do While my_switch = True
   If InStr(Worksheets("Sheet1").Range("A1").Value, "--") > 0 Then
      Worksheets("Sheet1").Range("A1").Value = Replace(Worksheets("Sheet1").Range("A1").Value, "--", "-")
   Else
      my_switch = False
   End If
Loop

Upvotes: -1

PaulG
PaulG

Reputation: 1199

Will a formula suffice to replace the -- with a -? Something like: 

=SUBSTITUTE(A1,"--","-")

In VBA, there are various ways, but here's one which will give you an idea of looping etc...

Option Explicit

Sub ReplaceDashes()

Dim r As Range, r1 As Range
Set r = Sheet1.Range("A1:A10")

For Each r1 In r
    If InStr(1, r1.Value, "--") >= 0 Then r1.Value = Application.WorksheetFunction.Substitute(r1, "--", "-")
Next

End Sub

Hope that helps..

Upvotes: -1

Related Questions