PDA

View Full Version : character adding



aoc
09-03-2008, 11:32 PM
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
09-04-2008, 12:05 AM
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
09-04-2008, 12:18 AM
Hi,

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

mdmackillop
09-04-2008, 12:20 AM
In a spare column enter ="d" & A1 and copy down. Copy/PasteSpecial Values over the original data and clear the helper column.

mdmackillop
09-04-2008, 12:24 AM
For i = iLastRow To 1 Step -1
No need to step backwards as you are not inserting/deleting rows.

aoc
09-04-2008, 12:35 AM
hi,

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

it will be g1204*. how can I do ?

mdmackillop
09-04-2008, 12:37 AM
Check out Replace in Help.

anandbohra
09-04-2008, 02:21 AM
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

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

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

Krishna Kumar
09-04-2008, 04:48 AM
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
09-04-2008, 05:31 AM
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 :clap: