What is the maximum number of characters you can concatenate together...

[xibalba]

... in a single cell?

I'm working in Abiword and Open Office Calc, and I want to know this so my spreadsheets play nice with Excel.

I have a 40 columns by 512 rows table of data, were each cell has two characters in it.

Also, does concatenation fail because of a limit on how many things can be concatenated together, or because there's a limit to how many characters can be in a cell?

One last thing: Is there a way to represent Windows line breaks as a character (or set of characters) in a cell?

[Cutter]

Hello xibalba, and welcome to the forum. And Happy 4th of July!

I believe the limit for the CONCATENATE() function (for Excel 2003) is 30. But you can avoid that by just joining things with an ampersand - like: =A1&B1&C1. That way you would just be limited to 1024 characters for a formula.

I'm not sure what you mean by: "Is there a way to represent Windows line breaks as a character (or set of characters) in a cell?"
If you mean, how do you get one in an Excel cell - you use Alt+Enter
If you mean, what is the character code - it's CHAR(10) in Excel

[xibalba]

Can concatenate work with ranges? I'm pretty sure it doesn't in Excel.

Though I was aware of using ampersand to concatenate things together, I wasn't aware that there's a difference between the ampersand and concatenate. Thank you!

So, if I wanted to get an massive table concatenated into a single cell, would I do something like this?

http://imgur.com/opuXX
http://imgur.com/Q3vIz
http://imgur.com/7uNqk

If I sort of stack the concatenation into formulas that are less than 1024 characters, then I can fit as many characters into a cell as I want?

[Cutter]

No, CONCATENATE() doesn't work with ranges.

The CONCATENATE() function is very rarely used because it's just easier to input using the ampersand.

Here is a link that shows Excel 2003's limitations: http://office.microsoft.com/en-us/ex...005199291.aspx

Note the: "Length of cell contents (text) - 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar."

[chiragshelat]

Excel version 2016 - Cell can hold up to 32,767 characters.
After that it will not add any characters. So if you use Concatenate or &, your last data may be truncated.

40 Column, 512 Rows and 2 characters in each = 40 * 512 * 2 = 40960 length ( e.g. LEN(A1) )

For your case: let's say Cell A1 has data A1, B1 has data B1,....J1 has J1,....AN1 has AN
For you first row combination will be A1B1C1D1E1F1...AN. So I am guessing you are not adding any comma or anything to separate them.

So if you concatenate by rows: concatenate 1st row which includes all 40 columns.
You can combine 409 full rows with all 40 columns. But you will have 23 full columns from 410th row that is total of 32766 characters. So you will have only one character from 24th column.

If you need to complete this entire thing, you can do in two separate part. 1st part will include 409 rows and 2nd part 103 rows and combined manually which cost you few additional minutes.

If you have to do this frequently, you can create a macro which inputs this in word or notepad file.

Hope this helps you.

[Pete_UK]

...Hope this helps you...

As the thread is nearly 5 years old, I doubt if the contributors are still monitoring it.

Pete

[chiragshelat]

You are right Pete_UK, I did not notice this as this is my first reply ever on any site.

[Pete_UK]

Don't worry about it - quite a few new members search for a thread that they can answer, without noticing the date it was started.

Welcome to the Forum, by the way.

Pete

[FDibbins]

I have done that myself before too LOL

But thanks for the input, Im sure members will find it helpful

[xibalba]

As the thread is nearly 5 years old, I doubt if the contributors are still monitoring it.

yC0RGID.png

Excel version 2016 - Cell can hold up to 32,767 characters.
After that it will not add any characters. So if you use Concatenate or &, your last data may be truncated.

40 Column, 512 Rows and 2 characters in each = 40 * 512 * 2 = 40960 length ( e.g. LEN(A1) )

For your case: let's say Cell A1 has data A1, B1 has data B1,....J1 has J1,....AN1 has AN
For you first row combination will be A1B1C1D1E1F1...AN. So I am guessing you are not adding any comma or anything to separate them.

So if you concatenate by rows: concatenate 1st row which includes all 40 columns.
You can combine 409 full rows with all 40 columns. But you will have 23 full columns from 410th row that is total of 32766 characters. So you will have only one character from 24th column.

If you need to complete this entire thing, you can do in two separate part. 1st part will include 409 rows and 2nd part 103 rows and combined manually which cost you few additional minutes.

If you have to do this frequently, you can create a macro which inputs this in word or notepad file.

Hope this helps you.

QxdjKCf.gif