Cell capacity issue-I think Excel 2007

[mrteater]

Hello I have a list of numbers (3480) and I am trying to put them in one, or two if I need based off of capacity. I just need to put them together which I will then transfer to a word doc. The numbers need to be separated by a comma with no extra spaces. I have attached what I have and then duplicated the date on an additional tab for you to play with. I run into a problem on line 94 where my formula stops gathering the data from the previous cells and the last number it collects is 8012132866. Any help would be appreciated.

Thanks

[sandy666]

no file attached


To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[mrteater]

Sorry about the delay on the attachment. I had to back off the formula from dragging it down to cell 3480 as it made the doc over 8mb and too big for this site. I updated it to line 150 so you could see how it stopped on line 94.

[Fluff13]

There is a max number of characters visible in a cell, which you have hit.
But if you put =Len(D94) in a cell & copy down, you'll see that the number of characters is increasing.
Whether you will be able to copy & paste them into Word, I'm not sure

EDIT:
Having just done a quick test using Ctrl C, Ctrl V the last full number carried across is 8015876111

[mrteater]

so can you help me with what the formula will be in cell e3?

[sandy666]

as result you need one single cell with all numbers concatenated and separated by comma?

[mrteater]

correct so they will look like 8012130605,8012130606,8012130612,8012130618,8012130625,8012130626,8012130631,8012130636,8012130655,8012130680,8012130773,8012132039,8012132066,8012132101,8012132103,8012132104..... Is there another formula I should use than what I am using? =IF(D3>0,E2&","&D3,D3). originally though I was maxing out at line 94 but that must just be a viewing limitation in excel. when we copy paste this to word it shows that the formula maxes out at line 2978. wondering if this is the best formula or something else I can do to eliminate me have to redo the formula at line 2979.

[Fluff13]

Maybe

[Roel Jongman]

Why not simple copy the whole list in word as text

and then do a search for special > paragraph marking and replace with comma.
that gives you a continues list of numbers separated by comma's you ask for

[sandy666]

Excel 2007 limitaition is ca. 32 000 characters per cell
with your example is a little more 38268 with commas

at once i can show 32765 so maybe split column and try it partially

or use post#9

[mrteater]

I believe the LEN function is telling me the number of characters are used in a particular cell. I'm not trying to determine that. I'm trying to determine if there is another formula than what I am using so that I do not have to repeat my formula at line 2879. My current formula is =IF(D3>0,E2&","&D3,D3). Originally thought I was maxing out at line 94 but that must just be a viewing limitation in excel. When we copy paste this to word it shows that the formula maxes out at line 2978. Wondering if this is the best formula or something else I can do to eliminate me have to redo the formula at line 2979. If not that is okay but just thought I'd check.

[sandy666]

max characters in formula = 8192
max characters in single cell (or result of formula) = ca. 32000 (depend of version of excel)
you have much more so you'll never see more than that

so use copy/paste to word file and change CR/LF to comma

[mrteater]

"Thanks! That solved my problem!"

[sandy666]

You are welcome

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.