Character adding

[aoc]

hi,

there are numbers in different cells. how can I add a character to the beginning of these numbers easily.

for example

7665m007
7874dt33
2011kf85

will be
d7665m007
d7874dt33
d2011kf85

[anandbohra]

paste the code in module & run
(pl change the sheet & range reference accordingly)

Sub addcharacter()
    Application.ScreenUpdating = False
    Dim iLastRow As Long
    Dim i As Long
    Dim addchar As String
    addchar = "d" '--Change as per requirement
    With Sheet1 '--Change as per requirement
        iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = iLastRow To 1 Step -1
            .Cells(i, "A") = addchar & .Cells(i, "A")
        Next i
    End With
    Range("a1").Select
    Application.ScreenUpdating = True
End Sub

[aoc]

Hi,

I tried but I get object required error. can you please check from the attached file

[mdmackillop]

In a spare column enter ="d" & A1 and copy down. Copy/PasteSpecial Values over the original data and clear the helper column.

[mdmackillop]

For i = iLastRow To 1 Step -1

No need to step backwards as you are not inserting/deleting rows.

[aoc]

hi,

if I want to add * symbol after 4 and delete k in g1204k

it will be g1204*. how can I do ?

[mdmackillop]

Check out Replace in Help.

[anandbohra]

hi,

if I want to add * symbol after 4 and delete k in g1204k

it will be g1204*. how can I do ?

Just replace existing line with the below one

[VBA].Cells(i, "A") = addchar & Left(.Cells(i, "A"), Len(.Cells(i, "A")) - 1) & "*"[/VBA]

the above code first delete last 1 character then add given character on starting & asterisk (*) at the end

[Krishna Kumar]

paste the code in module & run
(pl change the sheet & range reference accordingly)

Sub addcharacter()
    Application.ScreenUpdating = False
    Dim iLastRow As Long
    Dim i As Long
    Dim addchar As String
    addchar = "d" '--Change as per requirement
    With Sheet1 '--Change as per requirement
        iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = iLastRow To 1 Step -1
            .Cells(i, "A") = addchar & .Cells(i, "A")
        Next i
    End With
    Range("a1").Select
    Application.ScreenUpdating = True
End Sub

You could avoid looping. See

Sub kTest()
    Dim r   As Range, f As String
    Set r = Range("a1", Range("a" & Rows.Count).End(xlUp))
    f = "=""D""&" & r.Address & ""
    With r
        .Value = Evaluate(f)
    End With
End Sub

HTH

[mdmackillop]

You could avoid looping. See

Sub kTest()
    Dim r As Range, f As String
    Set r = Range("a1", Range("a" & Rows.Count).End(xlUp))
    f = "=""D""&" & r.Address & ""
    With r
        .Value = Evaluate(f)
    End With
End Sub

HTH

Neat