concatenate a large number of cells

instead of listing all the cells in a column that i want to concatenat
into one cell. 
(A1&A2&A3&A4&A5&A6 ETC.  I HAVE TO LIST MAYBE A HUNDRED CELLS in a
column. how can i list A1 to A100???

maybe this isn't clear but--thank

--
ROL
-----------------------------------------------------------------------
ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1606
View this thread: http://www.excelforum.com/showthread.php?threadid=27541

0
11/4/2004 8:17:49 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
726 Views

Similar Articles

[PageSpeed] 14

Rolg, here is a function by JE McGimpsey that should do it, if you need any
more help on how to use it just post back

Public Function MultiCat(ByRef rRng As Excel.Range, _
            Optional ByVal sDelimiter As String = "") As String
      'concatenate cells as a range
      'From:  JE McGimpsey
      'Call as   =MultiCat(A1:A5)
         'you can add a delimiter if you wish:
      '=MultiCat(A1:A5,",")

        Dim rCell As Range
        For Each rCell In rRng
            MultiCat = MultiCat & sDelimiter & rCell.Text
        Next rCell
        MultiCat = Mid(MultiCat, 1 - (Len(sDelimiter) > 0))
    End Function



-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003

"ROLG" <ROLG.1f7qvc@excelforum-nospam.com> wrote in message
news:ROLG.1f7qvc@excelforum-nospam.com...
>
> instead of listing all the cells in a column that i want to concatenate
> into one cell.
> (A1&A2&A3&A4&A5&A6 ETC.  I HAVE TO LIST MAYBE A HUNDRED CELLS in a
> column. how can i list A1 to A100???
>
> maybe this isn't clear but--thanks
>
>
> -- 
> ROLG
> ------------------------------------------------------------------------
> ROLG's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=16066
> View this thread: http://www.excelforum.com/showthread.php?threadid=275415
>


0
11/5/2004 12:36:24 AM
Reply:

Similar Artilces:

Using object model to coult the number of used rows
Hi Does any one know how to count the number of used columns and rows in a sheet via the object model I know int m_columncount = m_CurrentWorkSheet.Columns.Count; gives the total columns, but what I need is a count of the used colums. Or do I need to write a manual count i.e. manually count how many are used by by checking each element? thanks Andy Hi Andy, The following will get the number of rows in your used range as you would observer using Ctrl+End dim rowCnt as long rowCnt = activesheet.Cells.SpecialCells(xlLastCell).row Take a look at http://www.mvps.or...

Switching cells
Is there a macro out there where I can select array 1 and array 2 and switch their contents without having to copy/cut/past array 2 to array 3, array 1 to array 2, and then array 3 to array 1? Thanks, Byron -- Unsophisticated User No. If you replace item 1 with item 2 (item = cell or array element) then item 1 no longer exists unless you preserved it first. -- Jim "DrJBN" <DrJBN@discussions.microsoft.com> wrote in message news:BB198334-6453-4C89-89DE-30D21188BB23@microsoft.com... > Is there a macro out there where I can select array 1 and array 2 and > switch &...

Two lines of characters in one cell
After increasing the height of a row, how do I type two lines of text in one cell, Example: "Excess Wages 3rd Qtr." Thank You in advance. Linda Format|Cells. Check Wrap Text on the Alignment Tab. Alternatively, you can press Alt+Enter to force a new line in a cell. tj "LindaM" wrote: > After increasing the height of a row, how do I type two lines of text in one > cell, > > Example: "Excess Wages > 3rd Qtr." > > Thank You in advance. > Linda Hi use ALT+ENTER to isnert manual linebreaks -- Regards Frank K...

Display empty cells as gaps, zero, or connect in chart
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I recently switched from a PC to a mac. I want the chart to not leave gaps where there are blank cells in a series. On my PC, in the select data dialog box I could click on the &quot;hidden and empty cells&quot; button and choose to display empty cells as gaps, zeros, or connect data points with a line. On my mac, empty cells are displayed as gaps in the chart (xy scatter with line), but I want to connect the gaps and cannot figure out how to do that. <br><br>thanks in advance <br> kerrie Go to Exc...

Excel XP: Showing Trace Precedents Arrows for Multiple Cells...
....Is there a quick way to display more than one set of Trace Precedents arrows in a single worksheet? I can show the precedents one at a time and turn them off with one command, but I'd like to display selected sets (without having to repeat the action multiple times) before I email the file to another user. Is creating a macro the only way? Thanks in advance Amanda This is in Excel 2003 Help, but I've never been able to figure out exaaaaactly what it is trying to describe: <<To see all the relationships on a worksheet, in an empty cell type = (equal sign), and then c...

How do I reference a worksheet name in a cell, or vice versa?
If I want to label worksheet by date for example, and I want the date to show in cell A1, how can I either have cell A1 produce the worksheet tab name, or reflect that name within the cell? take a look at http://mcgimpsey.com/excel/formulae/cell_function.html In article <0364E13C-AAF2-4E30-9880-DF9655989B9A@microsoft.com>, ilmeaz <ilmeaz@discussions.microsoft.com> wrote: > If I want to label worksheet by date for example, and I want the date to show > in cell A1, how can I either have cell A1 produce the worksheet tab name, or > reflect that name within th...

Protect Cells 01-15-10
Is it possible to protect a cell after someone inputs data into it. I want to develop a sheet where people can sign up for a training session but after saving the file the cell(s) that have names in them, the cells cannot be changed. Yes. Let me assume that I wish to protect the following range of cells D 83 to D 85 (inclusive) in a particular worksheet. In EXCEL 2007 take the following action:- 1. Highlight all the cells in your Worksheet by clicking in the box to the left of A and above 1 in the top left hand corner. This should cause your Worksheet to become...

Macro for Deleting rows with balnk cell
Hi, i have several data in a sheet with data in 10 columns... in column A there are cells where it is blank and i want to delete the rows where there is blank cell in column A.. I need the vba macro for this regards, radha Try Sub DeleteBlankRows_2() 'This macro delete all rows with a blank cell in column A 'If there are no blanks or there are too many areas you see a MsgBox Dim CCount As Long On Error Resume Next With Columns("A") ' You can also use a range like this Range("A1:A8000") CCount = .SpecialCells(xlCell...

how do I change the lerrers in the header of each cell to a name?
I am trying to create a spreadsheet that names or at least name the firstcell so that it will be present even when I scroll down . is this possible? I think you are talking about Freeze Panes. Select cell A2, assuming you have headers or whatever in A1, then click on Windows - Freeze Panes. Now scroll down and you will see that row 1 stays visible at all times. Is this what you want? HTH Otto "robush3" <robush3@discussions.microsoft.com> wrote in message news:8664E670-87FF-486A-8E8A-4B51002B5ACF@microsoft.com... >I am trying to create a spreadsheet that names o...

Bad: tick mark in cell
I am trying to add a tick mark to an excel cell, in tick mark I am on about the ticks you would get from your techer when you got something right!! I have seen it on some sheets before but can't find where it's hidden. any help?? --- Message posted from http://www.ExcelForum.com/ dougal Set the font to Windings2 and use the letter P This will give you a tick. Andy. "dougal >" <<dougal.zz57w@excelforum-nospam.com> wrote in message news:dougal.zz57w@excelforum-nospam.com... > I am trying to add a tick mark to an excel cell, in tick mark I am on > about ...

mark active cell and return to paste
after macro is run ....i want to run another macro starting in the exact cell that the previous code ended in. mark active cell Range("R4").Select Selection.Copy return to "active cell" ActiveSheet.Paste No need to use select, it is just slowing down the code: Range("R4").Copy ActiveCell or the more descriptive one: Range("R4").Copy Destination:=ActiveCell Regards, Per "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> skrev i meddelelsen news:ade54b7a-3d1d-48ca-bbb6-8c0d730ad380@e7g2000yqf.googlegrou...

Is it possible to plot discontiguous cells?
Hi, I posted this yesterday in Miscellaneous, but thought it would be a good idea to ask here, too. I have a column which returns data from other columns based on a set of formulas. The result in each cell can be either a number (to plot in a line chart) or a blank and/or "-" sign like so: 5 - - 3 - - - 8 I would like to be able to chart only the numbers which result in this column. The dashes and numbers will not always position in the same manner as the calculations are altered by user input (therefore I cannot merely return the next number a fixed rows down into another colum...

How can I see all my wrapped text in a cell.
I have formatted the text to wrap in the cell and how adjusted the row height to auto-fit and even larger. I can see all of the text in the formula bar (counted 1336 words) but it does not all display in the cell. I have changed the size of the font and changed the vertical alignment. This leaves white space in the cell, but still does not display all of the information. Hi enter some manual linebreaks with aLT+ENTER every 1000 characters -- Regards Frank Kabel Frankfurt, Germany "Charlene Hoffmeister" <Charlene Hoffmeister@discussions.microsoft.com> schrieb im Newsbe...

creating 2 lines within one cell
I use a pc, and when I hit ALT Enter, I can get another line within one cell. How do you do that on a Mac? My boss can't seem to make it happen. Karen Hanson, Mohave Community College Try Command + Option + Enter Regards, Peo Sjoblom "Karen Hanson" wrote: > I use a pc, and when I hit ALT Enter, I can get another line within one cell. > How do you do that on a Mac? My boss can't seem to make it happen. Karen > Hanson, Mohave Community College ...

Make a cell blink
Hi there - I need a cell to "blink" or change back and forth from one color to another when a workbook is opened. I tried to do this with a macro, but when you run it, it goes so fast that you cannot see it blink. Any ideas on how to do this?? Thank you, Jen Hi You may use Sleep to control the blinking speed together with DoEvents. HTH Cordially Pascal "JenML5" <JenML5@discussions.microsoft.com> a �crit dans le message de news: C76EA65D-ABDB-45C2-A129-39B09FD9EE11@microsoft.com... > Hi there - > I need a cell to "blink" or change back and forth...

Cell Total shown Sheet to Sheet
Question: I have an expenses/income report that is setup as a monthly sheet... Can the final Monthly total (say it is in cell AF70) be reflected from Sheet one into Sheet 2 (into cell AF 68)?? Basically, the AF70 cell in every sheet should be refelcted into the next sheets cell AF68... Rinse and repeat for every sheet inthe excel file (12 sheets in all). Thanks for any comments or help... macgilgamesh -- macgilgamesh ------------------------------------------------------------------------ macgilgamesh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27930 ...

my page numbers are the same
I am typing an APA paper, all of my page numbers are the same in the header. How do I fix that? Don't type a number in the header; insert a PAGE field using the Insert Page Number button on the Header and Footer toolbar or the corresponding button on the contextual Header & Footer Tools | Design tab in Word 2007. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Waiithog" <Waiithog@discussions.microsoft.com> wrote in message news:855DB4FD-88CA-4477-BC0E-C9E6361760BA@microsoft.com... >I am t...

Convert text to numbers #2
I have a large spreadsheet with a column that has the numbers formatted as text. I tried reformatting the column from numbers to text but when I sum the column with other columns (that are formatted as numbers) it still doesn't show in the sum. The spreadsheet is too big for me to retype the numbers. Can anyone please help??? In a another column insert a formula that multiplies the first colum that you are having problems with by 1. i.e = a1*1 and drag all the way down. This will force Excel to create a result that is a number. Then copy this new results column on top of the old one...

query to delete negative numbers in HQ
Hello all, I have some negative items trapped in my HQ that are not in any of my stores inventories. I believe this happened when the supplier was changed. Is there a simple query to drop these negative numbers out of HQ?. This is a multi-part message in MIME format. ------=_NextPart_000_008C_01CA5950.12193CA0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Patrick, How about running a 501 worksheet and then a 190 task. --=20 Jeff=20 Check Point Software Search this newsgroup -- http://tinyurl.com/2lmk4w (Stolen from DRS) =3D=3D=3...

Cell formatting question
Hello, An easy question (hopefully!): How do I format cells in degrees/minutes/seconds using Excel 2003? For example, so that 202.5 is formatted as 202� 30' 0.0". Thanks. >>How do I format cells in degrees/minutes/seconds using Excel 2003? Excel doesn't have such a number format, sorry. -- Jim Rech Excel MVP Hi Sheila! The only way I can think of is to take advantage of the=20 time formats, but everything will be 24 times to big.=20 Here's what I did. If you've got all of your "rough=20 formatted" numbers in column A, in B1 put =3DA1/24 and copy...

Find a value inside a range of cells
Key words: Key word Category Code ACCIDENT 1 ACCOUNT 2 ACCREDITATION 3 ADMISSION 4 AGREEMENT 5 ALARM 6 ANNUAL 7 APPLICATION 8 APPRAISAL 9 ARREST 10 -------------------------------- NMAC DocumentType Category 1.15.3.101 NON-RECORD MATERIALS 1.15.3.102 SUBJECT FILES 1.15.3.103 PRIMARY MISSION RECORDS 1.15.3.104 AGENCY ORGANIZATIONAL CHARTS 1.15.3.105 ADMINISTRATIVE RULES 1.15.3.106 ADMINISTRATIVE FILES (EXECUTIVE LEVELS) 1.15.3.107 ADMINISTRATIVE REFERENCE FILES (NON-EXECUTIVE LEVELS) 1.15.3.108 ADMINISTRATIVE CORRESPONDENCE FILES (EXECUTIVE LEVELS) 1.15.3.109 GENERAL...

Customer Purchase Order Numbers on Invoices?
Our commercial customers require their purchase order number to appear on the invoices we generate. I do not see an area where this can be entered. I have the same problem and while it isn't perfect, I use the F9 method. By pressing F9 while ringing up the sale, you can enter a Reference # and a comment for the transaction which will print on the invoice. Here is the problem... there is no way to make PO# entry mandatory for specific customers so your employees must remember which customers to ask a PO# for, and this has been no end of problems for me. Also, this information is...

Best practice: international phone numbers
Are there any best practices regarding how to format phone numbers in Address Book within international company? I'm interesting about most compatible (and clear) format (if any) for users accross the world, especially for mobile users with PDA/Goodlink Today I have +9(999)999-999-99 009 999 999 99 999.999.99 +99 (0) 999-999-99 999.999.99 ext. 9999 and this is what I want to standardize somehow. Thanks. This format is international standard: +99 (999) 99999 and is the best way to sync with mobile devices. Please pay attention of the space before "(" and after ")&...

cell format #5
I have a vlookup formula. =IF(ISERROR(VLOOKUP($A7,avail,7,FALSE)),"",VLOOKUP($A7,avail,7,FALSE)) the formula is looking for a date. the formula works fine. but what is happening is that if it finds the item and then column 7 is blank. the formula returns the following. 01/00/00 how can i get it so it doesnot return anything at all if there is nothing in the column. What do you want to see? I'm guessing nothing: =if(iserror(yourformula),"",if(yourformula="","",yourformula)) alternatively since you want to see "" in either cas...

Automatic copying of data cells
Hi I am working on a spreadsheet that has a header at the top of each document, that includes Name, date, materials etc. I want the info that I type into the top header to always go to the other headers. Copying and pasting is too time consuming as there are numerous pages. How can i do this? Just link the other headers =[Book2]Sheet1!$A$1 Regards, Peo Sjoblom "DaleM" wrote: > Hi I am working on a spreadsheet that has a header at the top of each > document, that includes Name, date, materials etc. I want the info that I > type into the top header to always go ...