Excel to Word mail merge for envelopes. Should be super-simple. ISN'T.
August 23, 2013 10:11 AM Subscribe
Whoever is formatting this excel sheet sucks. I need to find a way to fix it that doesn't entail me retyping the whole thing each week.
Each week, we receive an excel document from our main office with names and addresses (usually about 30 a week) to which we have to send letters. The problem is that the name, address, city state zip is all in a single cell -- one per person -- and they're formatted so strangely that I don't even know what I'm looking at.
I'm pretty familiar with mail merge in Word using an excel spreadsheet as the data source, but this thing is formatted so weirdly that I cannot get it to make either a sheet of labels, or print the names/addresses on envelopes -- with or without Mail Merge Wizard.
Stumbling block to getting quick answers: I can't show you the excel document, because, well, names and addresses, but I can't overwrite it with fake data and upload a mock of it, because then it won't have the bizarre formatting it currently has (I really have no idea how it's done, but it's truly annoying).
Basically, the cells look like this:
COLUMN A:
ROW 1: BLANK
ROW 2: LASTNAME, FIRSTNAME
ADDRESS
CITY, STATE, ZIP
ROW 3: BLANK
ROW 4: LASTNAME, FIRSTNAME
ADDRESS
CITY, STATE, ZIP
ROW 5: BLANK
ROW 6: LASTNAME, FIRSTNAME
ADDRESS
CITY, STATE, ZIP
etc.
But none of the elements is delineated in any way. I don't know how they're making the line breaks, either, but when I try to merge the document to an envelope or label template, I get a square character with a question mark inside it where the line break would be--and everything runs together into a single line.
PLEASE, if you have any ideas at all, I would really appreciate them. This was just dumped on me as "your problem now," basically, and... you've gotta be kidding me with this. Who formats names and addresses in excel without creating individual fields for each element?!
Each week, we receive an excel document from our main office with names and addresses (usually about 30 a week) to which we have to send letters. The problem is that the name, address, city state zip is all in a single cell -- one per person -- and they're formatted so strangely that I don't even know what I'm looking at.
I'm pretty familiar with mail merge in Word using an excel spreadsheet as the data source, but this thing is formatted so weirdly that I cannot get it to make either a sheet of labels, or print the names/addresses on envelopes -- with or without Mail Merge Wizard.
Stumbling block to getting quick answers: I can't show you the excel document, because, well, names and addresses, but I can't overwrite it with fake data and upload a mock of it, because then it won't have the bizarre formatting it currently has (I really have no idea how it's done, but it's truly annoying).
Basically, the cells look like this:
COLUMN A:
ROW 1: BLANK
ROW 2: LASTNAME, FIRSTNAME
ADDRESS
CITY, STATE, ZIP
ROW 3: BLANK
ROW 4: LASTNAME, FIRSTNAME
ADDRESS
CITY, STATE, ZIP
ROW 5: BLANK
ROW 6: LASTNAME, FIRSTNAME
ADDRESS
CITY, STATE, ZIP
etc.
But none of the elements is delineated in any way. I don't know how they're making the line breaks, either, but when I try to merge the document to an envelope or label template, I get a square character with a question mark inside it where the line break would be--and everything runs together into a single line.
PLEASE, if you have any ideas at all, I would really appreciate them. This was just dumped on me as "your problem now," basically, and... you've gotta be kidding me with this. Who formats names and addresses in excel without creating individual fields for each element?!
Magnetsphere's idea is good, and also you can sort the rows to eliminate the empty ones.
posted by bleep at 10:23 AM on August 23, 2013 [1 favorite]
posted by bleep at 10:23 AM on August 23, 2013 [1 favorite]
Well, what I would do first is send them a blank Excel document laid out like you need it for Mail Merge to work, with column headers at the top, and ask if it's possible to send you the data in that format.
Assuming that won't work - they're likely inserting hard returns into each cell by using alt-enter between each line. I just googled and found this, which I tried and it does work for me: Highlight the column, go to text to columns, choose "delimited," and where it asks you to choose a delimiter, hold down alt while typing 010 into the box, then release alt and proceed. What you're doing there is using the hard return as the delimiter. It should separate each line into separate columns.
posted by something something at 10:23 AM on August 23, 2013
Assuming that won't work - they're likely inserting hard returns into each cell by using alt-enter between each line. I just googled and found this, which I tried and it does work for me: Highlight the column, go to text to columns, choose "delimited," and where it asks you to choose a delimiter, hold down alt while typing 010 into the box, then release alt and proceed. What you're doing there is using the hard return as the delimiter. It should separate each line into separate columns.
posted by something something at 10:23 AM on August 23, 2013
Agree with magnetsphere that Text to Columns is how to start. I would probably start by doing a find-and replace on the weird line break character (you might need to copy and paste it into the F&R box) and replace it with a comma, then do text to columns.
posted by athenasbanquet at 10:23 AM on August 23, 2013
posted by athenasbanquet at 10:23 AM on August 23, 2013
A neighbourhood park all covered with cheese: Whoever is formatting this excel sheet sucks
Can you work your way backwards and figure out who is generating this spreadsheet and then try to troubleshoot at the human level? Maybe they are getting raw data from some other source, and it would be easier for you to just work with that source.
posted by Rock Steady at 10:24 AM on August 23, 2013
Can you work your way backwards and figure out who is generating this spreadsheet and then try to troubleshoot at the human level? Maybe they are getting raw data from some other source, and it would be easier for you to just work with that source.
posted by Rock Steady at 10:24 AM on August 23, 2013
I would just sort the rows to isolate the empty ones, copy and past the rows with the info, and drive on.
posted by KokuRyu at 10:27 AM on August 23, 2013
posted by KokuRyu at 10:27 AM on August 23, 2013
For text to columns, I think you should also be able to cut/paste the weird character from one of the cells into the text to column dialog to define it as a delimiter.
posted by Perplexity at 10:34 AM on August 23, 2013
posted by Perplexity at 10:34 AM on August 23, 2013
I'm with Magnetsphere, Text To Columns, using the Commas as delimiters.
Trust me, this will take ALL of the pain out of your life.
First, add some blank columns to accept the data.
Select your column with all the data. Go to the Data tab. In the middle is Text To Column. Select Delimited. Click Next. Deselect Tab. Go to the box at the bottom Other and put in a comma. Then you'll get a preview. If you like it click Finish.
No need for thanks, just pay it forward.
posted by Ruthless Bunny at 10:40 AM on August 23, 2013
Trust me, this will take ALL of the pain out of your life.
First, add some blank columns to accept the data.
Select your column with all the data. Go to the Data tab. In the middle is Text To Column. Select Delimited. Click Next. Deselect Tab. Go to the box at the bottom Other and put in a comma. Then you'll get a preview. If you like it click Finish.
No need for thanks, just pay it forward.
posted by Ruthless Bunny at 10:40 AM on August 23, 2013
Text to Columns is incredible, I really don't know why more people don't know about it.
I'd definitely try the human option first, the number of times at work I've spent an hour a week working around someone else's formatting, when it turned out if I'd asked they had no preference and would happily switch. (Hanlon's razor: "Never attribute to malice that which is adequately explained by stupidity.")
You could try just messing around with the cell size and layout so you can print straight on the labels from Excel, but using text to columns then mail merging is definately the proper and neater way of doing it.
There are inch/cm to excel height/width converters online, or you can make a textbox the right size and line the cell dimensions up to it. Then all you need to do is split it from one list of 30, to the shape your label sheets are (3x7?).
Once you've got the sizing worked out, next week you just need to copy and paste-as-values your new data onto this formatting.
posted by chrispy108 at 10:42 AM on August 23, 2013 [1 favorite]
I'd definitely try the human option first, the number of times at work I've spent an hour a week working around someone else's formatting, when it turned out if I'd asked they had no preference and would happily switch. (Hanlon's razor: "Never attribute to malice that which is adequately explained by stupidity.")
You could try just messing around with the cell size and layout so you can print straight on the labels from Excel, but using text to columns then mail merging is definately the proper and neater way of doing it.
There are inch/cm to excel height/width converters online, or you can make a textbox the right size and line the cell dimensions up to it. Then all you need to do is split it from one list of 30, to the shape your label sheets are (3x7?).
Once you've got the sizing worked out, next week you just need to copy and paste-as-values your new data onto this formatting.
posted by chrispy108 at 10:42 AM on August 23, 2013 [1 favorite]
Response by poster: Text to Columns is not helping -- massive amounts of data are disappearing (just last and first names appear after conversion), and whatever-the-hell weird thing is causing the line breaks doesn't appear in such a way that I can copy it to paste it into the "Delimiter" field.
I'm new at the job; I can't start asking people to do things differently.
posted by A neighbourhood park all covered with cheese at 10:45 AM on August 23, 2013
I'm new at the job; I can't start asking people to do things differently.
posted by A neighbourhood park all covered with cheese at 10:45 AM on August 23, 2013
What happens when you export to csv? Once you do that, you can probably easily unpick the weird stuff and get it back into Excel in a usable format, and if you cannot, it's much easier to edit it and upload a few lines of mock data so people can help you.
To make a new line in the same cell, you hit Alt+Enter, which might also help in creating mock data to show.
posted by jeather at 10:50 AM on August 23, 2013
To make a new line in the same cell, you hit Alt+Enter, which might also help in creating mock data to show.
posted by jeather at 10:50 AM on August 23, 2013
A neighbourhood park all covered with cheese: I'm new at the job; I can't start asking people to do things differently.
It doesn't have to be like that. Whoever is sending them may think its a pain to get them in that format, and so if you tell them it is not working for you, you may be making their job easier. I realize that, depending on the situation, the human approach may not be possible for some reason, but you don't have to look at it as forcing people to change.
posted by Rock Steady at 10:51 AM on August 23, 2013
It doesn't have to be like that. Whoever is sending them may think its a pain to get them in that format, and so if you tell them it is not working for you, you may be making their job easier. I realize that, depending on the situation, the human approach may not be possible for some reason, but you don't have to look at it as forcing people to change.
posted by Rock Steady at 10:51 AM on August 23, 2013
Try this (assuming you're on a PC?):
Go to the find & replace window
In the "find" field, hold down ALT and type 0010
In the replace with field, make it a comma
Does that work to replace all the line breaks with commas? ALT+0010 is the default line break in Excel.
posted by brainmouse at 10:51 AM on August 23, 2013 [1 favorite]
Go to the find & replace window
In the "find" field, hold down ALT and type 0010
In the replace with field, make it a comma
Does that work to replace all the line breaks with commas? ALT+0010 is the default line break in Excel.
posted by brainmouse at 10:51 AM on August 23, 2013 [1 favorite]
Is there any way you could take a couple of the cells, replace the text manually with "Doe, Jane / 111 Oak Street / Boston MA 11111" "Smith, John / 222 Beech Street / Boston MA 1111" etc. and share the small sample? If you did even 5 or so, we could probably figure out some strategies.
And +1 to having a friendly conversation with the person who gives you the spreadsheet to see what their process is in generating it. If they're copy-pasting from a Word or PDF doc, you're probably SOL, but that's not necessarily the case.
posted by athenasbanquet at 10:55 AM on August 23, 2013
And +1 to having a friendly conversation with the person who gives you the spreadsheet to see what their process is in generating it. If they're copy-pasting from a Word or PDF doc, you're probably SOL, but that's not necessarily the case.
posted by athenasbanquet at 10:55 AM on August 23, 2013
Okay we can do this, and it won't suck either.
First, sort to remove the blank rows.
Then do the Find and Replace with Find = 010 and Replace =,
Then do the Text to Column thing.
posted by Ruthless Bunny at 10:59 AM on August 23, 2013
First, sort to remove the blank rows.
Then do the Find and Replace with Find = 010 and Replace =,
Then do the Text to Column thing.
posted by Ruthless Bunny at 10:59 AM on August 23, 2013
Response by poster: Try this (assuming you're on a PC?):
Yes, sorry; I should have put all of this info in the question.
Windows XP, Office 2007.
I tried doing a search for "010," but typing it while holding down "alt," but apparently it won't let you type in the search field if you hold down "alt."
posted by A neighbourhood park all covered with cheese at 11:11 AM on August 23, 2013
Yes, sorry; I should have put all of this info in the question.
Windows XP, Office 2007.
I tried doing a search for "010," but typing it while holding down "alt," but apparently it won't let you type in the search field if you hold down "alt."
posted by A neighbourhood park all covered with cheese at 11:11 AM on August 23, 2013
That's weird -- are you getting an error message or is it just pinging at you when you do it? If you type 0010 while pressing down alt, you shouldn't see any text appear, but it should still work.
posted by brainmouse at 11:18 AM on August 23, 2013
posted by brainmouse at 11:18 AM on August 23, 2013
I tried doing a search for "010," but typing it while holding down "alt," but apparently it won't let you type in the search field if you hold down "alt."
You're searching for a soft return (originally entered into the cell as ALT+ENTER); you won't see any text appearing in the search field when you enter ALT+010 but the cursor will change to a flashing dot and advance one space to indicate that it has some non-displayable thing it's searching for.
posted by stefanie at 11:25 AM on August 23, 2013
You're searching for a soft return (originally entered into the cell as ALT+ENTER); you won't see any text appearing in the search field when you enter ALT+010 but the cursor will change to a flashing dot and advance one space to indicate that it has some non-displayable thing it's searching for.
posted by stefanie at 11:25 AM on August 23, 2013
Response by poster: you won't see any text appearing in the search field when you enter ALT+010 but the cursor will change to a flashing dot and advance one space to indicate that it has some non-displayable thing it's searching for.
Nope. Just a constant "bong"-ing sound which means "nope, you can't type."
posted by A neighbourhood park all covered with cheese at 11:30 AM on August 23, 2013
Nope. Just a constant "bong"-ing sound which means "nope, you can't type."
posted by A neighbourhood park all covered with cheese at 11:30 AM on August 23, 2013
I wonder if the cells are merged? Do you know how to check that?
posted by peep at 11:41 AM on August 23, 2013 [1 favorite]
posted by peep at 11:41 AM on August 23, 2013 [1 favorite]
Did you try using alt-010 as the delimiting item in the text-to-columns process?
posted by something something at 11:52 AM on August 23, 2013 [1 favorite]
posted by something something at 11:52 AM on August 23, 2013 [1 favorite]
One other thing you might try is to copy the text from one of the address cells to Word, hit the little button to see the formatting, copy what you see there at the end of each line break, and paste that into the delimiting field.
posted by something something at 11:55 AM on August 23, 2013
posted by something something at 11:55 AM on August 23, 2013
Did you try copying and pasting from one of the cells? Try setting your cursor to right behind the last letter of FirstName and click-and-dragging to right before the first letter of Address, then hitting Ctrl-C. Sometimes (depending) it won't highlight in blue so it looks like you haven't copied anything, but try pasting into the Find box anyway....
posted by athenasbanquet at 11:56 AM on August 23, 2013
posted by athenasbanquet at 11:56 AM on August 23, 2013
Save as text and/or csv and then reopen the file in Excel to see if it will break the data into separate rows for you. If that doesn't work, try copying and pasting into notepad. Notepad will often strip out crazy formatting.
posted by soelo at 12:00 PM on August 23, 2013
posted by soelo at 12:00 PM on August 23, 2013
Couple of thoughts on the mail merge end of things. It would be more elegant to get the data formatted as data in excel, but if you can get Word to recognize the line breaks when it does the mail merge that seems like it would solve your problem also.
-Does it also strip the line breaks if you try to do a mail merge in Word without using a template?
-When you do Insert Merge Field within the envelope or label template, do you get a choice between inserting as an Address Field or as a Database Field? What happens if you pick database field instead of address field?
-You could also try copying the offending character and doing a find and replace to change it to a line break (use ^l to replace a character with a line break) in your merged results.
posted by yarrow at 12:02 PM on August 23, 2013
-Does it also strip the line breaks if you try to do a mail merge in Word without using a template?
-When you do Insert Merge Field within the envelope or label template, do you get a choice between inserting as an Address Field or as a Database Field? What happens if you pick database field instead of address field?
-You could also try copying the offending character and doing a find and replace to change it to a line break (use ^l to replace a character with a line break) in your merged results.
posted by yarrow at 12:02 PM on August 23, 2013
OK, this is a clunky "solution", but it should get you usable data:
posted by stefanie at 12:26 PM on August 23, 2013
- Copy/paste the column of cells to Word
- Convert from tables to text (Table Tools > Layout > Convert to Text)
- Convert the text back to tables (Insert > Table > Convert Text to Table); in the conversion dialog select Tabs as the text separator At this point you'll have the address data in separate rows instead of columns, which I don't think is useful yet, so tweak it some more:
- Convert from table to text again
- Convert back to tables; in the Convert Text to Table dialog change Number of columns to 3 and leave the text separator as Paragraph (this assumes you've already ditched the blank cells; if not, make it 4 columns and then delete the column of blanks after it's converted to a table)
- Copy/paste back to Excel
posted by stefanie at 12:26 PM on August 23, 2013
Did you try using alt-010 as the delimiting item in the text-to-columns process?
I just tried this and it worked for me. Assumes alt-Enter is how they got the line breaks within the cells.
posted by trip and a half at 12:28 PM on August 23, 2013
I just tried this and it worked for me. Assumes alt-Enter is how they got the line breaks within the cells.
posted by trip and a half at 12:28 PM on August 23, 2013
Here's the answer.
In the Find box, do Alt-J, then put a comma (,) in the Replace Box.
That is some old school shit there.
posted by Ruthless Bunny at 1:07 PM on August 23, 2013 [1 favorite]
In the Find box, do Alt-J, then put a comma (,) in the Replace Box.
That is some old school shit there.
posted by Ruthless Bunny at 1:07 PM on August 23, 2013 [1 favorite]
Response by poster: I've copied the column from excel to a blank word doc, and then converted table to text, and then turned on the ability to view all the formatting.
I'm going to try to take a screen-shot of the characters that are showing up, and blur out the words, because one of the formatting characters is just baffling to me. There are soft returns (the down-and-then-left arrow), and paragraph marks, as expected, but there's also this... weird... box-shaped thing that kind of looks like... I can't exactly explain it. It appears in all the blank cells.
posted by A neighbourhood park all covered with cheese at 1:17 PM on August 23, 2013
I'm going to try to take a screen-shot of the characters that are showing up, and blur out the words, because one of the formatting characters is just baffling to me. There are soft returns (the down-and-then-left arrow), and paragraph marks, as expected, but there's also this... weird... box-shaped thing that kind of looks like... I can't exactly explain it. It appears in all the blank cells.
posted by A neighbourhood park all covered with cheese at 1:17 PM on August 23, 2013
Is it the symbol that appears in any blank cell in any table in Word when you have view formatting on? (Just make a new doc and insert a table, and view formatting) -- if so, that's not a symbol you need to worry about, it's just a Word tables thing.
posted by brainmouse at 1:33 PM on August 23, 2013
posted by brainmouse at 1:33 PM on August 23, 2013
I don't know how you feel about using formulas, and particularly ones off a stranger on AskMe, but I've uploaded a spreadsheet that puts them into a line by line basis here.
To explain:
Column A has the original values in.
Column B (
The meat of Columns C to G is in e.g.
Columns H to M give the positions to start copying a given line ( e.g. cell I2 is
Then there's the rest of columns C to G, the bit where the string is tidied
I don't know if that's useful. I've a feeling, if you're new to Excel formulas, this is far too much, far too condensed, but you may be happy to use it as it is. There's plenty of assumptions I've made (chief being that you want to get rid of commas in address lines), but it may be possible for you to work with this.
posted by ambrosen at 2:17 PM on August 23, 2013 [1 favorite]
To explain:
Column A has the original values in.
Column B (
=INDIRECT("A"&(ROW()-1)*2) ) copies every other row.The meat of Columns C to G is in e.g.
MID($B2,H2+1,I2-H2), where it takes the bit of the copied address in $B2 (column B (the $B means B doesn't change when this formula gets copied) on line 2) and takes out the bit that H2 and I2 tell it to: it starts just after H2 and goes on for the difference between H2 and I2, which are the places where the newlines in $B2 areColumns H to M give the positions to start copying a given line ( e.g. cell I2 is
=IFERROR(FIND(CHAR(10),$B2,H2+1)),LEN($B2)) : looks for a CHAR(10) (newline), starting from just after where the last one was found (the value in H2, with 1 added so it starts looking just after). If it goes past the end, it just returns the length of $B2Then there's the rest of columns C to G, the bit where the string is tidied
=TRIM(CLEAN(SUBSTITUTE(..........,",",""))). The formulas are all nested, but the innermost SUBSTITUTE removes all the commas (replaces them with nothing). The CLEAN removes all the non-printable characters, and the TRIM removes any spaces from the result.I don't know if that's useful. I've a feeling, if you're new to Excel formulas, this is far too much, far too condensed, but you may be happy to use it as it is. There's plenty of assumptions I've made (chief being that you want to get rid of commas in address lines), but it may be possible for you to work with this.
posted by ambrosen at 2:17 PM on August 23, 2013 [1 favorite]
Metapad is a very slightly enhanced version of Notepad that adds, among a handful of other things, the ability to search for and replace carriage return / line breaks.
When I have problems like this I usually try to fix it via saving as plain text or .csv and performing some combination of search/replace to try to convert it into a .csv file that at least has things broken out into columns, then re-open it in Excel and further massage it by deleting or moving columns. (There are lots of other, sophisticated text editors, but Metapad might be the simplest to use with so few features.)
posted by XMLicious at 7:04 PM on August 23, 2013
When I have problems like this I usually try to fix it via saving as plain text or .csv and performing some combination of search/replace to try to convert it into a .csv file that at least has things broken out into columns, then re-open it in Excel and further massage it by deleting or moving columns. (There are lots of other, sophisticated text editors, but Metapad might be the simplest to use with so few features.)
posted by XMLicious at 7:04 PM on August 23, 2013
Regarding the beeping when entering the alt codes, are you using the numbers on top row of the keyboard or the number pad? The beeping makes me think you're using the top row, but you need to be using the number pad.
posted by yuwtze at 10:07 PM on August 23, 2013
posted by yuwtze at 10:07 PM on August 23, 2013
I deal with this all the time. In fact, I derive a chunk of my income from dealing with this exact issue - taking columnar formatted data and getting into a horizontal format that is parsed correctly. I read the comments upthread and all are fine, but this is how i deal with this problem. It is a bit shortened because it is late, but there are pictures.
Get ASAP Utilities. It is worth the download and it is shareware. (Pay for it if you use it a lot like I do.)
1) (For this initial step, all I am showing is how to get rid of extra spaces as shown in your post. If that spacing is not the real cell data, , you can ignore this first part. If not, highlight the cell with all the combined address data. Then from the ASAP menu, use the "Delete leading, trailing and excessive spaces" command to scrub out the extra spacing. It will get rid of all errant spacing throughout the data area. You could manually do it, but it is a pain. Otherwise, highlight the cell and COPY. Now get over to Word.
2) Getting the data into Word. Word is just plain better for certain scrubbing tasks than Excel. Highlight the entire list of names, copy. Take the whole thing from A1 down to the end of the actual data. Go to a new document in Word. Use PASTE SPECIAL as UNFORMATTED TEXT into Word. This will not only paste raw text, but will convert soft returns to hard returns, which you will use in the next step. You will now see a blob of text. DON'T MOVE ANYTHING. Just leave it as it is.
3) Still in Word, search for every instance of a double paragraph mark (^p^p – 2x caret and 2x lower case “p”) and replace it with some jibberish character set. I always use "zzzzq" as the likelihood of encountering that sequence in life is about nil. Again, DON'T MOVE ANYTHING. Just do the S&R.
4) Now S&R for every single paragraph mark (^p – caret and lower case “p”) and replace with a TAB (^t – caret and lower case “t”) .
5) Now S&R for "zzzq" and replace with a CARRIAGE RETURN (^p – caret and lower case “p”).
6) S&R for COMMAS "," and replace with a TAB (^t – caret and lower case “t”). Now you have something you can work with. ******Note: there may be some errant tabs at the first and last line(s). Just take them out manually.
7) In Word, SELECT ALL and COPY
8) Go back to Excel and PASTE SPECIAL as TEXT starting from cell A1. Now you have a parsed address list.
This all was done pretty fast, so if something does not make sense, just chime in. I will check back in the thread later.
And again, this is just how I do it. It is not the only way to skin this cat.
posted by lampshade at 3:05 AM on August 24, 2013 [1 favorite]
Get ASAP Utilities. It is worth the download and it is shareware. (Pay for it if you use it a lot like I do.)
1) (For this initial step, all I am showing is how to get rid of extra spaces as shown in your post. If that spacing is not the real cell data, , you can ignore this first part. If not, highlight the cell with all the combined address data. Then from the ASAP menu, use the "Delete leading, trailing and excessive spaces" command to scrub out the extra spacing. It will get rid of all errant spacing throughout the data area. You could manually do it, but it is a pain. Otherwise, highlight the cell and COPY. Now get over to Word.
2) Getting the data into Word. Word is just plain better for certain scrubbing tasks than Excel. Highlight the entire list of names, copy. Take the whole thing from A1 down to the end of the actual data. Go to a new document in Word. Use PASTE SPECIAL as UNFORMATTED TEXT into Word. This will not only paste raw text, but will convert soft returns to hard returns, which you will use in the next step. You will now see a blob of text. DON'T MOVE ANYTHING. Just leave it as it is.
3) Still in Word, search for every instance of a double paragraph mark (^p^p – 2x caret and 2x lower case “p”) and replace it with some jibberish character set. I always use "zzzzq" as the likelihood of encountering that sequence in life is about nil. Again, DON'T MOVE ANYTHING. Just do the S&R.
4) Now S&R for every single paragraph mark (^p – caret and lower case “p”) and replace with a TAB (^t – caret and lower case “t”) .
5) Now S&R for "zzzq" and replace with a CARRIAGE RETURN (^p – caret and lower case “p”).
6) S&R for COMMAS "," and replace with a TAB (^t – caret and lower case “t”). Now you have something you can work with. ******Note: there may be some errant tabs at the first and last line(s). Just take them out manually.
7) In Word, SELECT ALL and COPY
8) Go back to Excel and PASTE SPECIAL as TEXT starting from cell A1. Now you have a parsed address list.
This all was done pretty fast, so if something does not make sense, just chime in. I will check back in the thread later.
And again, this is just how I do it. It is not the only way to skin this cat.
posted by lampshade at 3:05 AM on August 24, 2013 [1 favorite]
This thread is closed to new comments.
Lastname
firstname/address/city
state
zip
Then a space as a deliminator in that firstname/address/city column that needed it. You'll be fine if the address gets split into multiple columns because you can just label them address 1, address 2, etc. then do multiple merge fields.
At least that would be my starting workaround for something like this.
posted by magnetsphere at 10:18 AM on August 23, 2013 [1 favorite]