Concatenating more than 30 items

Does anyone know how to get around the 30 item limit using the Concatenate
Function.  I know that you can get around the 7 level IF Function by
referring to another cell in the last False argument, but don't know how to
concatenate 2 cells using this Concatenate function.


0
gsiko (1)
11/5/2003 10:50:39 PM
excel 39879 articles. 2 followers. Follow

1 Replies
496 Views

Similar Articles

[PageSpeed] 49

You can use the concatenate operator.

Instead of:

    =CONCATENATE(A1,A2,A3,...,A30)

use

    =A1 & A2 & A3 & A4

or, perhaps more efficiently (I haven't ever checked):

    =CONCATENATE(A1, A2,...,A30) & CONCATENATE(A31, A32,...,A60)

In article <39fqb.70675$fl1.2910849@twister.southeast.rr.com>,
 "Gus Siko" <gsiko@nc.rr.com> wrote:

> Does anyone know how to get around the 30 item limit using the Concatenate
> Function.  I know that you can get around the 7 level IF Function by
> referring to another cell in the last False argument, but don't know how to
> concatenate 2 cells using this Concatenate function.
> 
>
0
jemcgimpsey (6723)
11/5/2003 11:12:05 PM
Reply:

Similar Artilces:

recover outbox items from user's pst
We had an exec's laptop crash while working offline with several emails she wants recovered sitting in the outbox. We were able to get the .ost file off the laptop. Is the solution as simple as recreating outlook in cached exchange mode and replacing the OST? found the answer in thread ost to pst. "knightly" wrote: > We had an exec's laptop crash while working offline with several emails she > wants recovered sitting in the outbox. We were able to get the .ost file off > the laptop. Is the solution as simple as recreating outlook in cached > exchange mode...

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

Calendar continues sending appointment messages to the Deleted Items folder (2nd post)
I have a user who reports her Calendar (Outlook 2000) continues sending appointment messages to the Deleted Items folder. Server is Exchange 5.5 SP4 server. These appointments have been added to the calendar before they have been automatically sent to the Deleted Items folder. She does not have the Resource Scheduling option under Tools->Options->Calendar set to automatically accept. There are no Outlook rules defined that affect appointment items. Apparently this is all default behavior. But how do we turn off this default? Outlook keeps sending unwanted calendar appointment mess...

Voiding a PO invoice should open the item rcpt for re-invoicing
If a posted PO invoice has been voided, the receipt on the PO should now be reset to be re-invoiced. You should not have to issue a return document to accomplish this (i.e. the invoice was matched incorrectly -- thus a void is in order). ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" ...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Cannot add item to invoice
Whenever I try to add a newly created item to an invoice I get the error, "This site is not assigned to the selected item. Do you want to assign this site?". I try to add the site and save it but when I click on quantity I get the same message. I've tried assigning the site in site maintenance and rolling down the changes as well as adding the site to the item in item maintenance. Nothing I do seems to work. I've been searching google and the ms newsgroups for weeks and have come up with nothing. I am desperate. Please, does anyone have any suggestions? Thanks, ...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

item discription on reciept
hey everybody, on some of the items we carry we have long item discription which the printer cuts when it generates the reciept for example if we have item description long pajamas blue color it only shows long pajamas b and cuts the rest does anybody know hoe i can change my XML reciept to maybe print discription into two lines instead of just one so i can get the whole description ...

7 files, 30 sheets, One summarry
Hi there This is a long one. Hope someone can help! I receive, on email & on a monthly basis, excel files from 7 countries. Each file has about 5-6 sheets for different sites in each country and a summarry sheet for the country. The data in the sheets is the same, i.e sales & volumes per site. The only way I can get the data is by email - we do not share a network + there is no one database I can extract the data from. Now each month I must consolidate this into one summary for the region (the 7 countries)and be able to present summary info(charts) for each country and/or fo...

IF and Concatenate
Hi I have a following chart which list out delivery dates arcross the top with items and units on the body of the chart (dashes are spaces) On the right column is the results that I need. What kind of formula can I use to return such results? I thought that I can use IF and Concatenate formula (IF, ordered units, then seek out date....). Not even sure I can use IF, since in my real chart, I have more than 20+ dates going across. Any help would be much appreciated! Style---7/30---8/13----8/14----8/15---8/17--------Wanted-Result 66106-------------------------------9--------5--------9 DUE ...

printing #30
I have a workbook in excel that has 3 spreadsheet tabs. I am selecting all 3 spreadheets and setting up a footer that has page # of #, that will print out and number continually all the pages (ex. 1 of 309, 2 of 309, etc). Problem, the numbering is correct but on some spreadsheets it is giving blank pages. Does all the settings as far as margins and the look of the spreadsheet have to be the same? This is why they were set up as 3 spreadsheets because of the different information. Thanks, No, but most likely if you printed ONE of the spreadsheets, it would print blank pages as wel...

Find Items Throughout Workbook
I own Excel 2000, but for some reason when I had to install it after my old computer crashed I couldn't use the "search workbook" function. (Edit-Find-look in workbook). Can someone let me know how I can get this function back? Tools > Commands > Edit then drag the Find button to your toolbar -- Gary''s Student - gsnu200909 "bintight" wrote: > I own Excel 2000, but for some reason when I had to install it after my old > computer crashed I couldn't use the "search workbook" function. > (Edit-Find-look in wor...

When concatenating concatenates don't concatenate... #3
Sometimes the simplest thing... I didn't realise that all the files ha to be open for the concatenation to work :rolleyes: . Doing s eliminates the problem and explains the erratic behaviour. And yes, th idea is to blend the variable and unvariable parts of an html pag together by pasting the final concatenation into a notepad.txt file Primitive, but interim. Thank you very much for your help. Best regards d'A -- d'A ----------------------------------------------------------------------- d'Az's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 ...

Getting popup CMenu item height
This should be a simple question, but I need help finding the answer: How do I figure out how many menu items will fit on the screen per column in a popup menu? Easy Answer: Get the screen height and the menu item height and divide. Screen Height: ::GetSystemMetrics(SM_CYSCREEN) Menu Item Height: ??????? I've tried ::GetSystemMetrics(SM_CYMENUSIZE) and ::GetSystemMetrics(SM_CYMENU) but they give incorrect answers. Does anyone know the correct function to call? Specifically: My screen is 1050 pixels tall (::GetSystemMetrics(SM_CYSCREEN) = 1050) By trial and error, I've determin...

Any way to add column for Message-ID to Inbox/Sent Items folders?
Hello, Is there any way, please, to add a column showing the Message-ID to the Inbox or Sent Items folders? My boss often receives return receipts from external recipients which show the sender's address and the Message ID, but not the subject. Outlook doesn't correlate them so he has to try and manually do it. Opening each message and looking in its Options takes too long. We're using Exchange 2000 and Outlook 2000 and 2003. Maybe Exchange 2003 does a better job of understanding notifications. Thanks, - Alan. "Alan" <bruguy@gmail.com> wrote in message news:...

Pasting multiple items from scrapbook
Hi, Im having a really hard time trying to paste multiple items from the scrapbook onto any office application. I have copied about 25 items onto the scrapbook but i can only paste them one at a time! is there a way where i can paste them all at once sort of the way i used to do with office clipboard's paste all option? If you want to paste everything at once, select an item in the scrapbook, Command+A (select all), then paste. If you want to paste a consecutive series of items (but not all), click the first, shift+click the last, then paste. If you want to paste several at the same t...

Sent Items issue
When i send an email they sit in the outbox instead of going to my sent items folder. The items that are in my sent items folder cannot be moved or deleted as it comes up with an error. Any help would be appreciated. Thank. T On Thu, 20 Jan 2005 06:41:04 -0800, ctdude wrote: > When i send an email they sit in the outbox instead of going to my sent items > folder. The items that are in my sent items folder cannot be moved or deleted > as it comes up with an error. Any help would be appreciated. Thank. What version of Outlook? What sort of mail account(s)? What is the error th...

dictionary->(item, item, item); Can dictionary point to an array in VBA?
Is it possible to have a dictionary object point to an simple array? Can you do something like that in code? Dim oDict as new Scripting.Dictionary oDict.Add "key_value", Array("foo", "bar") Tim "cate" <catebekensail@yahoo.com> wrote in message news:9f4b847e-eba7-487c-ac86-749d217dcc7e@b30g2000yqd.googlegroups.com... > Is it possible to have a dictionary object point to an simple array? > > Can you do something like that in code? ...

rev level in item inquiry
We need to be able to see the rev level of the part in the item inquiry screen. It would just link to the revision level field in the item engineering data screen. Currently a user with only inquiry access can not see what revision a part is supposed to be. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click &...

Listbox Delete multiple items
Hello. I'm trying to create a vba routine to delete items from a listbox. This is a multiselect listbox and what is happening is that i select several items and the code only removes the first item. When the first item is deleted, all of the other selected items became unselected. I post the code i'm using bellow. Can anyone give me a hint on this issue? Thanks, Luis Private Sub RemoveItem_btn_Click() If Listbox_1.ListIndex = -1 Then Exit Sub For i = Listbox_1.ListCount - 1 To 0 Step -1 If Listbox_1.Selected(i) Then Listbox_1.Remove...

Concatenate cells without specifying/writing cell address individually
Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&...

30, 60, 90 days late and due within 14 days
I am working on a spreadsheet that will calculate late suspenses. Basically I have 3 different types of late suspenses and am looking to calculate between 0 and 30 days late, 30 - 60 days late, 60 - 90 days late and over 90 days and each of these time frames for the three categories. Here is what my spread sheet looks like: Name ID TYPE SUSP DAYS OVER DUE UNIT WOOD 6470 N/A 18-Mar-05 (234) A SMITH 7453 UNIT 22-APR-05 (199) B JONES 9741 CO 1-Nov-05 (6) C ...

do not include discontinued items in reports
There should be an option with most Inventory reports to not include Inventory Items that are marked with an inventory type of Discontinued. Some reports allow to not include zero quantities, but sometimes there are discontinued items with a quantity which you do not want included in the report. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft ...

Concatenate with delimiter
I have some text that I need to concatenate, but with a delimiter. Three strings, A, B, C, where A and C are complicated expressions and B is a space-dash-space. I want the separator only if BOTH A and C are non-empty, otherwise only A or C (or nothing, if they're both empty). I've been playing with IsNull and NullIf functions, trying to influence the concatenation, but haven't come up with anything useful. Doesn't T-SQL have a function that is the opposite of Split? Pete -- This e-mail address is fake, to keep spammers and their address harvesters ou...

problem when transferring out serialized item
we have a serialized item that was returned by a customer, and was placed into offline inventory. i went to offline inventory and transferred the item out, we did a standard transfer type (we are sending the item back to the supplier). after doing that, i went to the transfer out order, and selected issue all, and tried to commit to it. i get the error message "the following serial number exists in the database, and is not available for issuing: 04215494019" i checked this serial number in the serial table, and the status of the serial number is set at 3. we've never...