Combining address lines for import - Carriage returns not processing properly

Daniel Noga 2 · November 2015

We did an Address Import some time back and ran into the issue of having an import file with the address lines split out into separate columns. When updating addresses through Import, however, it is a requirement that the address lines all occupy a single field (very frustrating, by the way). In order to combine the address lines for import, I followed the steps in KB article 41737 (found here: https://kb.blackbaud.com/articles/Article/41737 ). This article says that in order to separate the data intended for one address line from that intended for another address line, carriage returns ( /n ) need to be placed where a line break is desired.

When I ran the import and exported the addresses again to check them, I found that in some cases, RE: appeared to ignore the carriage return, treat it as part of the address, and lump all the address lines together. The result looks something like this:

Bank of Raiser's Edge/N1234 Fundraising Drive

Sometown, AA 00000

I have re-run this three times with the same result and I am not sure what else to try. The only thing I can think is that perhaps RE: reads /n differently from /N, but I was sure I factored for that in my most recent run.

I am so stumped.

Shannon Rajoelina · November 2015

Hey Daniel, you are correct that the /n feature is case sensitive. Double check that you did factor that in - I can't think of anything else that would make that feature not work. Luckily, a quick find and replace should let you quickly update your file if you find it is indeed capitalized. Good luck!

Daniel Noga 2 · November 2015

Thanks for confirming this, Shannon. Frazzled nerves and doing something for the third time often lead to overlooked details. I will double check this.

Daniel Noga 2 · November 2015

It worked! Yesss! Thanks again, Shannon.

Nice when solutions are simple.

Bradley Henry · November 2015

Here is a way to get the correct one cell address from up to 5 address line cells.

first you need to group all 5 address lines into one cell and add the /n for each line break:

=W4&"/n"&X4&"/n"&Y4&"/n"&Z4&"/n"&AA4

my address lines are in columns W,X,Y,Z,AA in this example just change the the cell tags to your cells.

the & is used to just create a string and the "/n" adds the page break indicator.

You should end up with data like this,

PO Box 8371/n/n/n/n

Oliphant Buidling/n60 Mills Road/n/n/n

You will see that there are multiple /n/n at the end of each line, the problem here is that it will import the breaks and you will have blank lines in your address. Don't worry this can be fixed with another formula.

=IF(RIGHT(AO4,8)="/n/n/n/n",LEFT(AO4,LEN(AO4)-8),IF(RIGHT(AO4,6)="/n/n/n",LEFT(AO4,LEN(AO4)-6),IF(RIGHT(AO4,4)="/n/n",LEFT(AO4, LEN(AO4)-4),IF(RIGHT(AO4,2)="/n",LEFT(AO4, LEN(AO4)-2),AO4))))

AO is the Cell that the above data is stored in, so what ever the cell is that you put the first formula is in, chage this formula to look at it.

In a nutshell what this is doing is, checking if the sell ends in /n/n/n/n if so it deletes the last 8 digits from the cell, if not it looks for /n/n/n and deletes the last 6 digits and so on. What you should get is this,

PO Box 8371

Oliphant Buidling/n60 Mills Road

The last step is to copy everything and paste into another sheet but use the paste as Values to remove the formulas.

once you have a spreadsheet setup with this formula you can at any time take your export data and past it into this sheet, auto fill down to get all the one line addresses and copy paste as value back into your import file.

Happy to help you set it up if you would like to message me directly.

Bradley.

Daniel Noga 2 · November 2015

This is top-notch guidance! It will take me a while to set up a sheet like this, but it sounds like it will save tons of time in the long run. Thank you for taking the time to explain all of that. I'll work on such a spreadsheet and let you know how it goes.

Alan French · November 2015

Bradley Henry:

You will see that there are multiple /n/n at the end of each line, the problem here is that it will import the breaks and you will have blank lines in your address. Don't worry this can be fixed with another formula.

=IF(RIGHT(AO4,8)="/n/n/n/n",LEFT(AO4,LEN(AO4)-8),IF(RIGHT(AO4,6)="/n/n/n",LEFT(AO4,LEN(AO4)-6),IF(RIGHT(AO4,4)="/n/n",LEFT(AO4, LEN(AO4)-4),IF(RIGHT(AO4,2)="/n",LEFT(AO4, LEN(AO4)-2),AO4))))

If you wanted to make this even more concise, rather that having a separate formula to take out the unwanted line breaks you could opt to only add the /n if there is a value in the next cell:

=A1&IF(B1="","","/n")&B1&IF(C1="","","/n")&C1&IF(D1="","","/n")&D1&IF(E1="","","/n")&E1

Bradley Henry · November 2015

Alan French:

Bradley Henry:

You will see that there are multiple /n/n at the end of each line, the problem here is that it will import the breaks and you will have blank lines in your address. Don't worry this can be fixed with another formula.

=IF(RIGHT(AO4,8)="/n/n/n/n",LEFT(AO4,LEN(AO4)-8),IF(RIGHT(AO4,6)="/n/n/n",LEFT(AO4,LEN(AO4)-6),IF(RIGHT(AO4,4)="/n/n",LEFT(AO4, LEN(AO4)-4),IF(RIGHT(AO4,2)="/n",LEFT(AO4, LEN(AO4)-2),AO4))))

If you wanted to make this even more concise, rather that having a separate formula to take out the unwanted line breaks you could opt to only add the /n if there is a value in the next cell:

=A1&IF(B1="","","/n")&B1&IF(C1="","","/n")&C1&IF(D1="","","/n")&D1&IF(E1="","","/n")&E1

Thanks Alan, I will try this with my import file.

Combining address lines for import - Carriage returns not processing properly

Comments

Categories