adding a semicolon

  • Thread starter Thread starter RaY
  • Start date Start date
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
 
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
 
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
 
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
 
Brilliant. Thanks for this. Nice and simple. Both methods work fine
though.

Cheers.

Ray.
 
Back
Top