adding a semicolon

R

RaY

I have a column of over 500 9 digit numbers. What I need is to add a
semicolon at the end of each individual number in its's cell - these
number references will be eventually copied into another application
to retrieve their associated data. I have tried to record a macro in
order to do this but it only replicates the same numbers (with the
semi colon) each time it is run.

Is there a way of of doing this ?

ActiveCell.FormulaR1C1 = "'385743341;"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "'538712414;"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "'603366470;"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "'455411095;"
ActiveCell.Offset(1, 0).Range("A1").Select

Thanks in advance.

Ray
 
H

Harald Staff

Hi Ray
Select the cells and run this:

Sub test()
Dim Cel As Range
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Value = Cel.Value & ";"
Next
End Sub

HTH. Best wishes Harald
 
G

Gord Dibben

Be careful with the ActiveSheet.UsedRange if you have more than the one
column of data in your sheet.

It will add the semi-colon to every cell in your worksheet, not just the one
column.

Maybe revise a bit.

Sub test()
Dim Cel As Range
For Each Cel In Range("A1:A500")
Cel.Value = Cel.Value & ";"
Next
End Sub


Gord Dibben MS Excel MVP
 
G

Gord Dibben

You can do this without VBA

Insert a column to the right of A and in B1 enter =A1&";"

Double-click the fill handle to copy down to B500

Copy and paste special>values then delete column A


Gord Dibben MS Excel MVP
 
R

RaY

Brilliant. Thanks for this. Nice and simple. Both methods work fine
though.

Cheers.

Ray.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top