 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Help needed with CONCATENATE Function.
I have a column of data where each cell contains a unique 8 digit number. I want to be able to put all of these numbers into a single string, with each number being separated by a ',', e.g. xxxxxxxx,yyyyyyyy,zzzzzzzz I know I could put a ',' in an empty cell, say C5 and use =CONCATENATE(A1,C5,A2,C5,A3,C5....) But with 112 rows this would be time consuming and would bust the 30 text strings that the CONCATENATE function allows. I'm sure there must be an easier way to do this but don't know what it is. Any suggestions gratefully received. Instead of the function, use the concatenation operator, &: =A1&C5&A2&C5&A3&C5.... still tedious ... If any of your numbers have leading zeros and are not text values, then you will have to use: TEXT(cell,"00000000") to preserve them as 8 digits. Hope this helps. Pete On Aug 6, 4:39 pm, "Chris Mitchell" <chris.a.mitch...@NOSPAMbtinternet.com> wrote: > I have a column of data where each cell contains a unique 8 digit number. > [quoted text clipped - 10 lines] > I'm sure there must be an easier way to do this but don't know what it is. > Any suggestions gratefully received. Use a helper column to concatenate a "," onto the end of each of your numbers, then in F1 type the cell where you want the big concatenation to be..... then highlight the cells with the number-comma values and fire this macro....... Sub ConcatenateSelection() Dim rng As Range Dim strConcat As String For Each rng In Selection strConcat = strConcat & rng.Text Next Range(Range("f1").Value) = strConcat Range(Range("f1").Value).Select End Sub The big concatenated string will be put in whatever cell address you put in F1 Vaya con Dios, Chuck, CABGx3 > I have a column of data where each cell contains a unique 8 digit number. > [quoted text clipped - 10 lines] > I'm sure there must be an easier way to do this but don't know what it is. > Any suggestions gratefully received. Thanks Chuck, this will be another learning curve for me as I'm not up on macros so have to learn how to include the text given. Should 'f1' in the macro be 'F1', i.e. a reference to cell F1 or does the case make no difference? > Use a helper column to concatenate a "," onto the end of each of your > numbers, [quoted text clipped - 33 lines] >> is. >> Any suggestions gratefully received. The case of the "F" does not matter, except for appearance............ See here for help getting started with macros....... http://www.mvps.org/dmcritchie/excel/getstarted.htm Vaya con Dios, Chuck, CABGx3 > Thanks Chuck, this will be another learning curve for me as I'm not up on > macros so have to learn how to include the text given. [quoted text clipped - 39 lines] > >> is. > >> Any suggestions gratefully received. >I have a column of data where each cell contains a unique 8 digit number. > [quoted text clipped - 7 lines] > But with 112 rows this would be time consuming and would bust the 30 text > strings that the CONCATENATE function allows. Using a helper column (say column B), put this in B1.. =TEXT(A1,"00000000") and put this in B2... =B1&","&TEXT(A2,"00000000") and copy it down as far as needed. Rick Thanks Rick, does exactly what it says on the tin. > >I have a column of data where each cell contains a unique 8 digit number. >> [quoted text clipped - 19 lines] > > Rick
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.