concatenation formula for long column of variables
Posted by jason on January 12, 2001 12:01 PM
I have a long column of part numbers in an excel column that I want concatenated into one long string with the format "pn1, pn2, pn3". I know how to work concatenate where i could type the formula =concatenate(A1,", ",A2,", ",A3), but I would like to have a formula that would take the large column and do it automatically for a range (ie. =concatenate(A2:A1000) doesn't work).
Any ideas?
Posted by Mark W. on January 12, 2001 12:35 PM
Enter =IF(ROW()=1,A2,OFFSET(B2,-1,)&","&A2) into
cell B2 and copy down. Use the value in the last
cell in column B.
Posted by Mark W. on January 12, 2001 12:43 PM
Oops! Make that:
=IF(ROW()=2,A2,OFFSET(B2,-1,)&","&A2)
Posted by Aladin Akyurek on January 12, 2001 1:06 PM
Mark: This leads to a circular reference if you start to apply it from B1 on. Applying it from B2 on, you loose the value in A1 in the result. Jason's problem isn't clear to me. I've just put in A1 thru A3 the values p1, p2, p3 and applied the formula from B1 as well as from B2.
Supposing this is what Jason wants, wouldn't we run up against the maximum length of string that Excel allows?
Aladin
Posted by Mark W. on January 12, 2001 1:25 PM
Aladin, the assumptio was that it would be input into
cell B2 and then copied down.
Yes, he'll max out the string length... but how
quickly depends on the # of part numbers and their
length. I figured I'd let Jason figure that out
then I'd propose that he:
1. format column a as @","
2. save as text only
3. open the file in a text editor and replace all
the carriage returns (or linefeeds) with nothing.
Posted by Mark W. on January 12, 2001 1:28 PM
Hey, I've been too busy...
to work on the anagram. Hopefully, I'll find time
tomorrow.
