Adding commas to the end of a word

[walls07]

I am total newbie when it comes to working with excel and I would appreciate if someone could help me with the following two problems I am having -

1. In a single column I have a list of random words e.g in column A, I have 1000 words going vertically down, in columns 1 down to a 1000. I would like to know how I can add a comma to the end of each word automatically?

2. In column A I have a list of 2,609 words going vertically down (the amount of words could be any random amount - even and odd numbers). In column A for the first 20 words I would like to automatically put the words "GROUP 001" in Column B next to the first 20 words in column A, thus the words "GROUP 001" would be repeated 20 times in column B. For the next 20 words in line in Column A, I would like to put the words "GROUP 002" in Column B, thus the words "GROUP 002" would be repeated 20 times in column B. In this example there are 2,609 words so 2,609 divided by 20 = 130.45, so there would be GROUP 001 to GROUP 131 in column B.

I hope this is not to much of a challenge.
Thanks in advance
Stephan

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 9"><meta name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/STEPHA%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><link rel="Edit-Time-Data" href="file:///C:/DOCUME%7E1/STEPHA%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_editdata.mso"><!--[if !mso]> <style> v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style> <![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0; mso-font-charset:2; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:0 268435456 0 0 -2147483648 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} h1 {mso-style-next:Normal; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; page-break-after:avoid; mso-outline-level:1; font-size:12.0pt; font-family:Arial; mso-font-kerning:0pt; text-decoration:underline; text-underline:single;} p.MsoBodyTextIndent, li.MsoBodyTextIndent, div.MsoBodyTextIndent {margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:18.0pt; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:18.0pt; mso-bidi-font-size:12.0pt; font-family:Arial; mso-fareast-font-family:"Times New Roman";} a:link, span.MsoHyperlink {color:blue; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {colorurple; text-decoration:underline; text-underline:single;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:1079213119; mso-list-type:hybrid; mso-list-template-ids:1592588648 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:36.0pt; mso-level-number-position:left; text-indent:-18.0pt;} @list l1 {mso-list-id:1271158131; mso-list-type:hybrid; mso-list-template-ids:-1506882872 67698693 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;} @list l1:level1 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:36.0pt; mso-level-number-position:left; text-indent:-18.0pt; font-family:Wingdings;} @list l2 {mso-list-id:1546134073; mso-list-type:hybrid; mso-list-template-ids:-381932998 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l2:level1 {mso-level-tab-stop:36.0pt; mso-level-number-position:left; text-indent:-18.0pt;} @list l3 {mso-list-id:1661076764; mso-list-type:hybrid; mso-list-template-ids:1695190306 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l3:level1 {mso-level-tab-stop:36.0pt; mso-level-number-position:left; text-indent:-18.0pt;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} --> </style>[FONT=&quot]<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/> </v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" style='width:233.25pt; height:175.5pt'> <v:imagedata src="file:///C:/DOCUME~1/STEPHA~1/LOCALS~1/Temp/msoclip1/01/clip_image001.png" o:title=""/> </v:shape><![endif]--><!--[if !vml]--><!--[endif]-->[/FONT]

 

[nbrcrunch]

take a look at the CONCATENATE() function.

 

[Ron Coderre]

If your list starts in Cell A2...try this:

B2: ="GROUP "&TEXT(CEILING((ROW()-1)/20,1),"000")

copy that formula down as far as you need.

Is that something you can work with?

 

[Domski]

Try:

="GROUP "&TEXT(ROUNDUP(ROW(A1)/20,0),"000")

Copied down your list.

Dom

 

[walls07]

ADVERTISEMENT

Thanks for your quick response but I have already looked into this function and I still cannot figure out how to do what is required.

 

[walls07]

my last message was for nbcrunch let me try these out and I will get back to you. Thanks

 

[Kaps_mr2]

ADVERTISEMENT

1. B1 = A1&"," - will put a comma at the end of the word in cell A1 and store the result in cell B1.

2. B1 ="GROUP "&REPT("0",(3-LEN(QUOTIENT(ROW(),20))))&(QUOTIENT(ROW(),20)+1)

Thks

Kaps

 

[walls07]

Domski, I have just tried your out and worked perfectly, I am well impressesed, thanks for that and thank to everyone else who posted here, last question -

In a single column I have a list of random words e.g in column A, I have 1000 words going vertically down, in columns 1 down to a 1000. I would like to know how I can add a comma to the end of each word automatically?

Thanks
Stephan

 

[Domski]

You can add a comma using a formula like =A1&"," in a spare column and then just copy...paste special...values the results over the original list.

Dom

 

[walls07]

Kaps,

Is there anyway of doing it without storing the results and just adding the comma directly to the word in the cell, whether it be cell A,B or C?

Stephan