Code to change first digit in a 3 digit number

[Marklarbear]

Hi Brains Trust....

Hopefully this is a simple one - what would the VBA code be to change the first digit of a 3 digit number to change it to 5 ?

ie in the example below - in cell A2 it will change the number from 706 to 506, cell A6 from 880 to 580 and so on through to the bottom of the list.

cheers

 

[Biz]

Try code below

Sub ChangeFirstDigitToFive()
    Dim rng As Range
    Dim cell As Range
    
    ' Set the range to the desired column where the numbers are located
    Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    ' Disable screen updating and calculation to improve performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the value has 3 digits
        If Len(cell.Value) = 3 Then
            ' Change the first digit to 5
            cell.Value = "5" & Mid(cell.Value, 2)
        End If
    Next cell
    
    ' Re-enable screen updating and calculation
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Does it help?


Kind Regards

Biz

 

[alansidman]

Here is an alternative to VBA. Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-US"), "Column1", Splitter.SplitTextByPositions({0, 1}, false), {"Column1.1", "Column1.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Position", "Custom", each 5),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1.1", "Column1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US"),{"Custom", "Column1.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

[kevin9999]

Assuming column A always contains 3 digit numbers, the following is offered as an alternative that doesn't require a loop.

Sub Start_5()
    With Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .Value2 = Evaluate("""5"" & right(" & .Address & ",2)")
    End With
End Sub

 

[JoeMo]

If you have a long list of numbers this non-looping code may be noticeably faster and it allows for positive and negative numbers as well as any other values.

Sub Marklarbear()
Const N As String = "5"  ' sets the replacement for the leading digit
Dim V As Variant, R As Range, i As Long, B
Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
V = R.Value
For i = 1 To UBound(V, 1)
    If IsNumeric(V(i, 1)) And Len(V(i, 1)) = 3 Then
        If V(i, 1) < 0 Then
            B = -1
        Else
            B = 1
        End If
        V(i, 1) = B * (N & Right(V(i, 1), 2))
    End If
Next i
R.Value = V
End Sub

 

[Marklarbear]

Hi guys - I just realised that I miss wrote my question originally (I didn't realise this until i reviewed the responses - my bad)


My revised question is:


Hopefully this is a simple one - what would the VBA code be to change the first digit of a 3 digit number to change it to 5 if it is greater than 600?

ie in the example below - in cell A2 it will change the number from 706 to 506, cell A6 from 880 to 580 and so on through to the bottom of the list.

The value in cell A2 wont change, the value in cell A8 wont change and so on - only the values greater than 600 will change to a 5** number.

apologies for incorrectly writing my original question.....

 

[Rick Rothstein]

Give this macro a try...

Sub StartWithFive()
  Dim Addr As String
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Addr = .Address
    .Value = Evaluate("IF(" & Addr & ">599,500+MOD(" & Addr & ",100)," & Addr & ")")
  End With
End Sub

NOTE: I assumed you meant that 600 should be changed to 500 also. If that is not correct, then change the 599 to 600 in my code.

 

[JoeMo]

Hi guys - I just realised that I miss wrote my question originally (I didn't realise this until i reviewed the responses - my bad)


My revised question is:


Hopefully this is a simple one - what would the VBA code be to change the first digit of a 3 digit number to change it to 5 if it is greater than 600?

ie in the example below - in cell A2 it will change the number from 706 to 506, cell A6 from 880 to 580 and so on through to the bottom of the list.

The value in cell A2 wont change, the value in cell A8 wont change and so on - only the values greater than 600 will change to a 5** number.

apologies for incorrectly writing my original question.....

Try:

Sub Marklarbear()
Const N As String = "5"  ' sets the replacement for the leading digit
Dim V As Variant, R As Range, i As Long
Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
V = R.Value
For i = 1 To UBound(V, 1)
    If IsNumeric(V(i, 1)) And Len(V(i, 1)) = 3 And V(i, 1) > 600 Then
        V(i, 1) = N & Right(V(i, 1), 2)
    End If
Next i
R.Value = V
End Sub