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

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 lpsca@earthlink.net
0
lpsca (4)
6/25/2004 11:12:00 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
860 Views

Similar Articles

[PageSpeed] 47

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

"Larry" <lpsca@earthlink.net> wrote in message news:450b4100.0406251512.55a2836e@posting.google.com...
> 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 lpsca@earthlink.net
0
jim.coneXXX (771)
6/26/2004 12:04:41 AM
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

"Larry" <lpsca@earthlink.net> wrote in message news:450b4100.0406251512.55a2836e@posting.google.com...
> 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 lpsca@earthlink.net
0
jim.coneXXX (771)
6/26/2004 12:06:29 AM
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

willwest22@yahoo.com

"Larry" <lpsca@earthlink.net> wrote in message
news:450b4100.0406251512.55a2836e@posting.google.com...
| 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 lpsca@earthlink.net


0
willwest22 (109)
6/26/2004 12:14:29 AM
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 ads@EZ-sell.net ./that is a public email address ... it's all over my wesite if anyone really wants to snatch it ........
thanks for you help
-- 
fat n sassy granny


"William" wrote:

> 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
> 
> willwest22@yahoo.com
> 
> "Larry" <lpsca@earthlink.net> wrote in message
> news:450b4100.0406251512.55a2836e@posting.google.com...
> | 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 lpsca@earthlink.net
> 
> 
> 
0
6/26/2004 10:25:02 PM
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 wrote:
> 
> 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 ads@EZ-sell.net ./that is a public email address ... it's all over my wesite if anyone really wants to snatch it ........
> thanks for you help
> --
> fat n sassy granny
> 
> "William" wrote:
> 
> > 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
> >
> > willwest22@yahoo.com
> >
> > "Larry" <lpsca@earthlink.net> wrote in message
> > news:450b4100.0406251512.55a2836e@posting.google.com...
> > | 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 lpsca@earthlink.net
> >
> >
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/27/2004 12:16:33 AM
THANKS Dave!  This will be saving me a lot of work! Thought I was going to have to spend hours with Video Prof again!
-- 
fat n sassy granny


"Dave Peterson" wrote:

> 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 wrote:
> > 
> > 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 ads@EZ-sell.net ./that is a public email address ... it's all over my wesite if anyone really wants to snatch it ........
> > thanks for you help
> > --
> > fat n sassy granny
> > 
> > "William" wrote:
> > 
> > > 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
> > >
> > > willwest22@yahoo.com
> > >
> > > "Larry" <lpsca@earthlink.net> wrote in message
> > > news:450b4100.0406251512.55a2836e@posting.google.com...
> > > | 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 lpsca@earthlink.net
> > >
> > >
> > >
> 
> -- 
> 
> Dave Peterson
> ec35720@msn.com
> 
0
6/27/2004 11:48:02 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

parameter for text
In Access 2003. In a form I have a parameter to search for a word at a a time Like [Froms].[Form].txtWords]&"*" which works well. .. In order to enter more than two oarameter separated by commas in txtwords I have been tryin the Str function like this "Instr([txtWords])" "Instr[txtWords]" The query displays no records at all. Could you suggest me how to do it Many thanks I would suggest separating by space and using this -- Like "*" & [Froms].[Form].txtWords] &"*" Remember that when you type in 'bell...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Symbol Updating Only Every Few Days (if at all)
Using Money 2006, and have a symbol "VLO" that is only updating every few days. This stock was a duplicate (downloaded transaction created a new version of the same stock - my fault not clicking correct choice when asked). I've removed the symbol from the stock entry that was downloaded, renamed this entry to something bogus, deleting this renamed stock "from all accounts", then added the symbol back to the original VLO stock that I've been tracking for years. Now the stock just says "unch" in the portfolio view, and the price history is only updat...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Adding a word to the end of other words at the same time
I was wondering if there was a way to add a word to the end or beginning of multiple other words in Excel. Example; say I have these 3 words.... Alpha Beta Tera Now I want to add LLC to the end of each word but I want to change them all at the same time. Like Alpha LLC Beta LLC Tera LLC Is there a way to do that? Phil Its Excel 2003 try Sub addtexttoend() For Each c In Selection c.Value = c & " xxx" Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "phil" <ptukey@charter.net> wrote in message news:1125340358.873337.4240@g44g2000cwa.googlegroup...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

How do I bind a XAML text box control to a dataset?
Hello; I am new to using WinFx and I am having trouble figuring out how to bind a text box to a field in a dataset. I found an MSDN article: http://msdn2.microsoft.com/en-us/library/ms752057.aspx My question deals with the text box code: <TextBlock Text="{Binding Path=ISBN}" Grid.Column="1" /> How does this text box know to bind to the field "ISBN" in "myDataSet"? How does the control implement the dataset? Thank you Monty ...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Selecting the Right Text Alignment for a edit box doesn't work
When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong Thanks Dan Dan, "Dan" <anonymous@discussions.microsoft.com> a �crit dans le message de news:DECFE605-A130-416B-9924-60BA0C79D684@microsoft.com... > When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong? > I've no idea :-))) You can open your RC-file as text, and make sure it has the ES_RIGHT style set, thus: EDITTEXT IDC...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...