URGENT:Concatenating 1000 cells into 1

E

es9004

I need to concatenate a 1000 cells into a single cell. Could someon
give me a macro to help me do that? Or if you have any ideas do let m
know.

Thank
 
W

William

Hi

Sub test()
Dim c As Range, s As String
s = ""
'Amend range as appropriate
For Each c In Range("A1:A1000")
s = s & " " & c
Next c
s = Trim(s)
Range("B1") = s
End Sub

--
XL2002
Regards

William

[email protected]

| I need to concatenate a 1000 cells into a single cell. Could someone
| give me a macro to help me do that? Or if you have any ideas do let me
| know.
|
| Thanks
|
|
| ---
|
|
 
H

Harlan Grove

I need to concatenate a 1000 cells into a single cell. Could someone
give me a macro to help me do that? Or if you have any ideas do let me
know.
Click to expand...

Unless the text in each of these 1000 cells is fairly short, the result won't
fit into a single cell. What do you get from

=SUMPRODUCT(LEN(Your1000Cells))

?
 
A

Anders Silven

What do you get from
=SUMPRODUCT(LEN(Your1000Cells))
Click to expand...


Excuse me for interrupting Harlan, this is not my thread, but I get the number
of characters in the range, regardless if the range contains numbers or text or
both. What do you expect?

Anders Silven
XL2002
 
D

Dave Peterson

Excel's cells can hold about 32k characters.

Harlan's formula will show what the length of the final concatenated cell would
be.

If it exceeds 32k, then the OP would be in trouble.
 
H

Harlan Grove

Anders Silven said:
Excuse me for interrupting Harlan, this is not my thread, but I
get the number of characters in the range, regardless if the range
contains numbers or text or both. What do you expect?
Click to expand...

I expect what you stated. That's why I asked. It's a diagnostic. Look up the
word 'diagnostic' in the dictionary, and you may gain enlightenment (but the
odds are against it).
 
Top