Welcome To The Connected Business Community Forum.
0 votes
34 views
Our old system stores customer street addresses in 3 separate fields in the DB.  I have concatenated these together to account for the single address field in CB, using Char(10) to provide the line breaks as needed.  The single address field looks fine in excel, but formats as a single "run-on" line in CB on import.

How should I format in excel so CB recognizes the line breaks?
asked May 11, 2017 in Customer by T.J. | 34 views

2 Answers

0 votes
Works now.  Discovered it requires both char(13) & char(10) for each line break in that order.
answered May 11, 2017 by anonymous
0 votes
I just thought this might help;

In Excel, if you have multiple source address lines (in this example 5) use this formula to concatenate in to one cell for import in to CB.  It is based on row 2 in a spreadsheet and assumes the first line of address (cell A2) is populated. B2, C2, D2 and E2 contain the other address lines and F2 contains this formula; (copy column F and paste as text in to spreadsheet used for importing)

=CONCATENATE(A2,IF(LEN(B2)=0,,CONCATENATE(B2, CHAR(13), CHAR(10))),IF(LEN(C2)=0,,CONCATENATE(C2, CHAR(13), CHAR(10))),IF(LEN(D2)=0,,CONCATENATE(D2, CHAR(13), CHAR(10))),IF(LEN(E2)=0,,CONCATENATE(E2, CHAR(13), CHAR(10))))

I hope this helps someone
answered Jan 27 by nonsibi (200 points)
131 questions
164 answers
190 comments
47 users