Conditional replace part of a numeric or string value

[Nite0wls]

Dear All,

I have a large sheet that does contain usage date of telephone numbers.

To uniform all numbers to an normalized format so I need to replace nrs starting with 56xx to nr 852392556xx
There are many more changes that I need to make so I use a macro for this that does all the conversions.

The problem that I come across is that when I do a replace of 56 to 852392556 all 56 entries form a match for this so

913560344 is transformed to 913852392556 0344
5693 is transformed to 85239255693
5656 is transformed to 852392556852392556
and so on.

Is there a way to use regex '^' so it only replaces the entries that start 56?

Thanks for your replies

 

[ParamRay]

Try this code:

Sub ChangeNumbers()

    Dim TelRange As Range
    Dim Cell As Range
    
    'Set range containing telephone numbers
    Set TelRange = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues)   'Amend accordingly
    
    For Each Cell In TelRange
        With Cell
            If Left(.Value, 2) = 56 Then
                .Value = 8523925 & .Value
            End If
        End With
    Next Cell

End Sub

 

[Nite0wls]

This works quite well, but I noticed that some fields in the other columns (B-F) are also changed and that is not required
Can I limit the range to the A column only?

 

[caabyyc]

=substitute(a1,"56","852392556")

 

[ParamRay]

This works quite well, but I noticed that some fields in the other columns (B-F) are also changed and that is not required
Can I limit the range to the A column only?

Yes, to set the range to column A only, change the following line...

From:

Set TelRange = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues)

To:

Set TelRange = Worksheets("Sheet1").Columns("A").SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues)

 

[Nite0wls]

Fabulous, that is exactly doing what I was trying to achieve.

Many, many thanks!