Adding a period to the end of cell contents

**ntallman** · 05-13-2010, 11:07 AM

Hi all,

Is there an easy way to add a period to the end of each cell's contents in a particular column? I have a column with over 500 rows and need to add a period to the end of the data in each row. There must be an easier way to this other than manually, perhaps using VBA?

Thanks!
Nathan

**Astroboy142** · 05-13-2010, 11:10 AM

Can you give an example?

Right now from what I'm thinking you could put this in a cell ="."

Example would help though

**daddylonglegs** · 05-13-2010, 11:11 AM

One way......assumes data in A1:A500....

Put this formula in B1

=A1&"."

and copy down to row 500 (if you place the cursor on the bottom right of B1 until you see a black "+" and then double click that will copy down automatically)

Now copy column B and use Edit > Paste special > values to convert formulas to values

Now you can delete the original column A

**Astroboy142** · 05-13-2010, 11:14 AM

there is the =CONCATENATE(....) formula as well

**NBVC** · 05-13-2010, 11:18 AM

there is the =CONCATENATE(....) formula as well

The & between the two items is essentially concatenating the items....

**day92** · 05-13-2010, 11:29 AM

Here is a macro that will do the trick.

Sub AddPeriod()
    Dim n As Long, LastRow As Long, c As Range, ActiveCol As Integer
     
    ActiveCol = ActiveCell.Column
    LastRow = Cells(65536, ActiveCol).End(xlUp).Row
     
    For Each c In Range(Cells(1, ActiveCol), Cells(LastRow, ActiveCol))
        If Right(c, 1) <> "." Then c = c & "."
    Next c
     
End Sub

Let me know if you have any questions