Problem with Concatenate - Results are too long for CSV

[DrewPaik]

I have a report with multiple columns that need to be concatenated into a
single cell for import into a CRM. I can't just concatenate a range because
I need to insert line breaks and punctuation between the various columns. I
also need to convert into a CSV before importing.

Because there are so many columns (only about 20), I have to break up the
concatenate function into 3 separate columns, then concatenate the resulting
3 columns. That's where I get the error:
"The text string you entered is too long. Reduce the
number of characters used or cancel the entry"

Even though I get the error, the final cell seems to work, but when I save
as a CSV file for import into my CRM, the cell gets truncated to only 256
characters or so. I've tried copying and pasting the values before
converting to CSV, but that didn't help either.

I've had this problem with Excel 2003 and Excel 2007.

 

[sb1920alk]

What CRM are you using? Are the different columns different fields? If they
are, you should be able to map the input tempate to match the existing column
headers without clumping them all together. Or is this a "notes" field or
similar and you're combining multiple exising notes from an old system into
one big note on the new system?

 

[DrewPaik]

Sorry for the delay, I didn't have notification activated.

I'm using Salesforce.com, but the problem is before I try importing. The
problem is in Excel itself.

Here's an example of the formulae:
Column AN... =CONCATENATE("- ",T$1,": ",T2," - ",S$1,": ",S2," -
",U$1,": ",U2," - ",V$1,": ",V2," - ",Z$1," ",Z2," - ",AA$1,"
",AA2," - ",AB$1," ",AB2," - ")
Column AO... =CONCATENATE(AC$1," ",AC2," - ",AD$1," ",AD2," - ",AE$1,"
",AE2," - ",AF$1," ",AF2," - ",AG$1," ",AG2," - ",AH$1," ",AH2," -
",AI$1," ",AI2," - ")
Column AP... =CONCATENATE(AJ$1," ",AJ2," - ",AK$1," ",AK2," - ",C$1,":
",C2," - ",D$1,": ",D2," - ",E$1," : ",E2," - ",F$1,": ",F2," -
",L$1,": ",L2)
Column AP... =AN2 & AO2 & AP2

I basically need to concatenate over a dozen columns (with headers) into a
single notes field. But in order to do that, I had to break it up into
several concatenate functions, then combine at the end.

Regardless, this USED to work, but now it doesn't - Excel complains that I
have too many arguments. When I save as a CSV, it truncates the field to
only the first 256 characters.

Any ideas?

 

[Dave Peterson]

Don't use the =concatenate() function. Use the & operator.

=concatenate(a1,b1,c1)
would be replaced with
=a1&b1&c1

And create a macro that exports the data the way you want.

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(or maybe you could build your own formula and copy|paste into Notepad.)

Check out Earl's Text Write program first. It may do exactly what you want
right out of the box.

 

[sb1920alk]

I agree with Dave: use &, it's much easier. Before you go too much farther,
can salesforce handle more than 256 characters in the notes field? If it's
limited on their end then this is a moot point.

With large amounts of data it could still be earier to break it up with
helper columns as you've done.

My office switched crm services around a year and a half ago, and this is
very similar to what I had to do then to transfer the existing leads. If you
need anything else, just let me know.