Adding a coma to the text data in every row of a column

[Larry]

Adding a coma to the text data in every row of a column

I have an Excel spread sheet that has 6,000 rows of text in one of the
columns. I want to be able to add a coma (,) after the last character
in each row.

Can this task be done automatically?

In additional to posting your solution to this group, can you please
cc: me at (e-mail address removed)

 

[Jim Cone]

Larry,

The following code did it for me in about 1 second on 7000 rows...

'-----------------------------
'Adds a comma at the right end of each cell's contents in Column "L" (12).
'For a different column change the L and 12 _
' to the appropriate characters.
'Cells below the last cell with data are not evaluated.
'Blank cells are omitted.
'Formulas are replaced with the value of the formula.
'A message box is displayed to the user before the code is run _
' and provides an option to exit the code.
'No error handling code is included.
'Jim Cone - June 25, 2004

Sub PutEmThere()
Dim objCell As Range
Dim objRng As Range
Dim lngMsg As Long
Const strFill As String = ","

Application.ScreenUpdating = False
Set objRng = Range("L1", Cells(Rows.Count, 12).End(xlUp))
lngMsg = MsgBox("A comma will be added to each cell in " & objRng.Address & " ", _
vbOKCancel + vbInformation, " Larry Made Me Do It")
If lngMsg = vbCancel Then GoTo StartExitHere

For Each objCell In objRng
If Len(objCell.Value) Then _
objCell.Value = objCell.Value & strFill
Next 'objCell

StartExitHere:
Application.ScreenUpdating = True
Set objCell = Nothing
Set objRng = Nothing
End Sub
'--------------------------------------------

Something extra: It is usually best to disguise your email address, _
to prevent spammers from harvesting it.

Regards,
Jim Cone
San Francisco, CA

 

[Jim Cone]

Larry,

The following code did it for me in about 1 second on 7000 rows...

'-----------------------------
'Adds a comma at the right end of each cell's contents in Column "L" (12).
'For a different column change the L and 12 _
' to the appropriate characters.
'Cells below the last cell with data are not evaluated.
'Blank cells are omitted.
'Formulas are replaced with the value of the formula.
'A message box is displayed to the user before the code is run _
' and provides an option to exit the code.
'No error handling code is included.
'Jim Cone - June 25, 2004

Sub PutEmThere()
Dim objCell As Range
Dim objRng As Range
Dim lngMsg As Long
Const strFill As String = ","

Application.ScreenUpdating = False
Set objRng = Range("L1", Cells(Rows.Count, 12).End(xlUp))
lngMsg = MsgBox("A comma will be added to each cell in " & objRng.Address & " ", _
vbOKCancel + vbInformation, " Larry Made Me Do It")
If lngMsg = vbCancel Then GoTo StartExitHere

For Each objCell In objRng
If Len(objCell.Value) Then _
objCell.Value = objCell.Value & strFill
Next 'objCell

StartExitHere:
Application.ScreenUpdating = True
Set objCell = Nothing
Set objRng = Nothing
End Sub
'--------------------------------------------

Something extra: It is usually best to disguise your email address, _
to prevent spammers from harvesting it.

Regards,
Jim Cone
San Francisco, CA

 

[William]

Larry

I assume that "I want to be able to add a coma (,) after the last character
in each row" means "I want to be able to add a coma (,) after the last
character in each cell"

Sub test()
Application.ScreenUpdating = False
Dim c As Range
'Amend range as necessary
For Each c In Range("A1:A1000")
c = c & ","
Next c
Application.ScreenUpdating = True
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Adding a coma to the text data in every row of a column
|
| I have an Excel spread sheet that has 6,000 rows of text in one of the
| columns. I want to be able to add a coma (,) after the last character
| in each row.
|
| Can this task be done automatically?
|
| In additional to posting your solution to this group, can you please
| cc: me at (e-mail address removed)

 

[greenapple]

I am totally lost in Excel .... I also need to put commas behind my data in each cell. I bought a Contact Mgmt SW & need to get over 4000 email address out of Outlook into the PIM. Can you give me a 'laymans' step by step ... i.e -- click on ...etc. I have gone to conditional formating..but get lost in the first box . If you want to email me the info, do so at (e-mail address removed) ./that is a public email address ... it's all over my wesite if anyone really wants to snatch it ........
thanks for you help

 

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Open your workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out--Tools|macro|macros...|Run

=====
Another way:

insert a new column adjacent your your data (say your data is in column A and
you insert a new column B).

Then put this in B1:

=if(a1="","",a1&",")

and drag down as far as you want.

If you want you can select column B, Edit|copy, then select column A and
Edit|paste special|Values.

Then delete column B (or just keep both of them there if it doesn't hurt.)

 

[greenapple]

THANKS Dave! This will be saving me a lot of work! Thought I was going to have to spend hours with Video Prof again!