have you ever noticed that once in a while you are feeling really comfortable and familiar with a particular application whether it's word or Excel and you start out on a project only to realize that something has happened that may be very quirky and something you weren't expecting at all once in a great while you may run into this issue and it's not the easiest to fix so we're going to look at how to rearrange an Excel address list that you've probably inherited or somebody's volunteered and gi
ven you and how to arrange that using word which actually winds up a little bit easier than trying to write a macro I'm Terry one of the consumer technology Specialists for Mid-Continent Public Library and let's look at those Excel lists how we can convert them to text open them in word make our fixes and then pull them back into Excel so that they are useful files as you start your new project and this has something to do with Excel and using names and addresses in a list you find that you are
just excited to have someone volunteer to help you with typing all of the names and so when you receive the workbook and you open it up what you are anticipating is something that looks like this where you have the names and sometimes even just first name and last name into separate columns you have the street and another column City and another state and zip separated into columns as well and while this appears to be kind of a small list of only oh about 10 people or so this is only a sampling
of what you may end up using let's say that we're anticipating say a thousand person list so if anything goes wrong with this list yes you could retype a 10 person list but a thousand person list is going to be a little more challenging and so you are just so excited thinking that this is the kind of list that you are going to get but in fact what happens is you receive a list that looks like this now the person who typed it knew that you wanted to use Excel and they did and you wanted names and
addresses and they most certainly typed names and addresses but somehow the communication broke down and they didn't realize and maybe they don't use Excel a whole lot so they went with how you would probably type these names and addresses in word for example they've gone vertically down the worksheet in one row you have the first and last name in the next row you have the street address and in the next row you have the city state and zip all combined together well again 10 people this might no
t be a huge deal a thousand people this could be a real challenge for time and how am I going to fix this and I wish there were some way that we could write a formula that would magically fix this kind of a layout we probably could write a macro that would work but if you're not familiar with macros then what you may decide to do is of all things use word as your kind of translator to make this look like it should now as we look at the information I do want to make it clear that we don't have an
yone here who has any kind of a title at the beginning of their name so there's no doctor or Mr or Mrs or Ms or anything like that there are also no middle initials that we might need to deal with and there are no I guess it's generational abbreviations at the end of a name there are no Juniors or seniors or I suppose titles there too for example PhD but once you know the basics of how to clean up this list you'll be able to apply what you've learned and need to adjust for all of those extra kin
ds of things that may happen we're going to take this list convert it to a text file and we'll look at the possibilities of the text files and what that means eventually we'll go to word which has an amazingly expanded find and replace ability and then we're going to save that word file as a text file again and bring it back into Excel so that we can use it save for a mail merge data source the first thing that we need to do when we have this Excel file and we want to in some way get this file t
o word so we can work with it we need to convert it to a text file and there are actually two text file type I guess we'll say contenders one is a CSV a comma separated values file the other kind of text file has the file extension txt and it is when we choose the right kind of text file a tab delimited file meaning that where we see the column boundaries in Excel in word we'll see tabs instead and one of those file types works much better with what we're trying to do than the other let's look a
t both and then decide which is the best so to start off I'm going to go up to file and then I'm going to come down to save as and I need to browse I'm going to save this on my desktop so I'll click on desktop on the left here I think I'm going to instead of calling this address list original I think we'll call it original one and then where it says save as type instead of excel workbook we're going to click the drop down and we're going to look for CSV and there are several kinds here there's C
SV dos CSV Macintosh but if I go up just a little bit there is CSV comma delimited and now that it says CSV comma delimited we'll click save and when we do that first of all we want to look at our title bar and it does say address list original one so we know that this is the correct file but we're going to get this yellow bar that says some features might be lost now the whole nature of a text file is that a lot of fancy things get kind of Stripped Away so you truly just have text what we can d
o is just say I don't want to preserve it in Excel so I'm not going to do a save as and as a matter of fact I'm going to go to the far right here find that X to close that bar out and then I'm going to because really we've kind of saved this we're going to in the upper right close now looking at my desktop I can already get a hint of what has happened when I saved as a CSV because here's my address list original and then here is the CSV file original one and notice that they both have an Excel i
con well let's look at original one I'm just going to double click on it to open it and we're right back to excel even though remember Excel really isn't how we want to work with this file so I'm going to go ahead and close out of address list original one and then I'm going to go back to my address list original because I do want to keep a copy of everything that we've done here but remember that we also have the option of tab delimited files so let's go to file save as and again I'm going to b
rowse here and make sure that I select my desktop and we're going to give this a very original name we're going to call this address list original two but this time instead of excel workbook I'm going to go to the far right click the drop down and I'm going to look for a text file now just like we saw the csvs if we go far enough in the list we have text for dos text for Macintosh but as we go up the list we can eventually find text tab delimited so we're going to select that and as it says text
tab delimited we are going to save to the desktop and we are going to once it is popped open we still get the same messages here about how we have a text format now as you see in the yellow we don't want to save it as Excel so first of all we'll go to the right and close out that message just for making it a little easier to see then I'm going to go to the top right and click the X to close that file now let's find it and I'm going to need to move it down to the center here a little bit you can
tell right away that this original 2 is different the icon is different isn't it when I double click on address list original 2 it opens that file in Notepad so it is really looking at the kind of word processing side of things rather than the Excel and number side of things the only thing is while it opened very nicely in notepad if I go to edit and I try to do anything very fancy here you'll see that find and replace just don't have a lot of options so we're not going to use notepad for what
we want to do because remember we want to pull all of these different entries that are now vertical we want to make them horizontal instead so I'm going to cancel and then we're going to close out of notepad and our original two because a double click on the file that is saved on my desktop here opens it in notepad I need to think about how I can open this file and get it into word well maybe it's an obvious answer what we need to do is come down to our start button here and then I have word in
my tiles so I'm just going to click on it there to open Word and then on the left we're going to select open and browse and I'm going to be sure to go to my desktop and I am looking for a certain kind of file now what happens is down at the bottom when I am searching with word it says file name and then over at the right chances are it's going to generally look for all files or it's going to look for Word Documents but it's going to be relatively rare that it would default to say text files so w
e need to in this drop down look for text files and it's quite a ways down here a little bit more than half anyway but select text files and then it shows me on the desktop the different kinds of files that are text files so from here address list original two and I can click on it and then click open and it opens that file in word and we can see that it is very vertical and this looks good in word doesn't it because this is kind of a format that word would use but we do have one issue here wher
e we have the city state and zip for some reason in the translation process we now have quotes around those three fields and as I scroll down that's about the only thing that's really noticeable that I'm seeing we may pick up other things as we go along one other item of note I have made sure that between the state and the Zip There are two spaces instead of only one now that makes it much easier for us in our example today I have found through working this kind of process before that if you hav
e a lot of names and addresses even if the final product is not going to be on legal paper sometimes I will convert to legal paper because of just the amount of information that is there I don't think we need to do that today however another nice trick in working with this kind of file is to go to layout in the tabs above the ribbon and first of all I think we'll go to orientation and go to landscape instead of portrait and then because of how the information lays out we're also going to go to m
argins and go to narrow for now with really large amounts of information I've not only switched from letter to legal size paper but I've also even changed those margins to as small as a quarter of an inch just to make sure that all of the data is fitting right and I can see what's going on and so now that we have the basic page layout working let's start and actually do some of the fixing that we need to do so we can pull this back into Excel let's first of all simply do a find and replace and r
emove the quotation marks around the city state and zip so I'm going to go back to home and of course we could use control F for find and then there's always a replace tab right next to it or if we go on this Home tab to the editing group we can go directly to replace here as well so we can click on replace and what do we want to find well we want to find a quote what do we want to replace it with and this is really interesting if I put in a space using my space bar then it would replace it with
a space but I don't want to replace it with anything because I have pulled down this text box it will replace these quotes with nothing so first of all let's go kind of slow here let's do a find next and it finds the very first quote in that very first name and then I can replace and as we see there's no space in front of East it just moved everything over where that quote was as I go to the right then it shows us the next quote now if you know your information well enough there's no reason to
go to find next replace find next replace you can simply replace all which is what we will do and we've made a lot of Replacements here well two for each entry and we'll click ok and now as we look at our address list you can see that those quotes are gone from every one of our entries well the next thing that I think we need to do is to look at that comma that is separating the city and the state and again we want to find a character in this case the comma I think we want to replace the comma w
ith a tab because we're really using a tab delimited file the tab takes the place of the border between two columns but how do I put in a tab I can't type a tab and have it work what I can do is come down to the bottom left of this window where it says more and click on it and then down at the bottom we have this whole replace section where we can look for formats and replace them so it's not only just fonts but it's paragraphs and tabs and language and all sorts of things but to the right of th
at we also have special and when we click on special the second choice here is a tab character now let's take a second and look at this too because look we could add in and find and replace in word paragraph marks tab characters section characters column breaks different kinds of dashes manual line breaks page breaks all sorts of different kinds of things can be found and replaced by using these special characters well we'll go with tab character so now we're saying though that the comma between
city and state is going to be replaced by a tab character and this is the code the symbol for the tab character let's look at find next and it started one off of the top but that's okay it's found that comma and we can replace and look at that we have space now between the city and the state it wasn't there before and it's going down to the next comma in fact let's replace all and they made nine Replacements well that's just about right since we have 10 people and their addresses now it becomes
a little difficult at this point to see what that extra space is all about but fortunately we have the show hide button on the Home tab in this paragraph Group which is the third group from the left we can go to the paragraph Mark kind of button that's called show hide and when we click on show hide it not only shows us the end of each line which is that paragraph mark but it shows the spaces if we have those with a little Dot and in this case here is our first tab it's an arrow pointing to the
right and so we now have that tab that separates the city and the state we've taken off the quotes well what else can we do here what I would like to do is find those two spaces now again you have to know your data because if there are two spaces any place else then you would probably need to do the fine next replace more manually than what we are going to do but let's go back to find what I'm going to delete the comma and I'm going to press my space bar twice but it's kind of hard to see it ju
st looks like invisible stuff and we are going to replace that with a tab as well well let's do the first one let's do a find next and it has found those two dots for the two spaces and we're going to trust that the rest of this works equally well and so we're going to select replace all and it's made nine Replacements but we're far enough in the list now that it says do you want to continue from the beginning we'll say yes and all done as we look at this and I'm going to close my find replace w
indow here we now have a tab between the city and the state and another tab between the state and the zip what I'd like to do next starts off as a little bit of a puzzle because what I would like to do is take those paragraph marks at the end of each line and replace them with tabs as well so I can really start getting those three lines of name and address going across for Excel my only problem is that if I do that when I come down to the end of a record we have paragraph Mark then a space and t
hen a paragraph mark if I do the single paragraph marks first then when I get here I'm going to do tab space Tab and that isn't what we want to do what you can do is sometimes get a little tricky and what we are going to do is first of all take that paragraph mark space paragraph and substitute in a pipe a symbol that is very rarely used and I know is not used within this particular list so let's go back to replace and what do we want to find well we want to go down to special because again I ca
n't type a paragraph Mark but I can go to special and select paragraph mark press my space bar and then another paragraph mark and what do I want to replace it with I want to find the pipe which is on my keyboard it's a shift and then it's the key above my enter key so I'm going to shift and make that a pipe a straight line now let's see how it works let's go to find next and so it has found paragraph mark space paragraph mark when I replace it's kind of bunched everything up but if we look clos
ely in here we can now see that we have the first person's name in all of their information and then just before the next person's name we have that straight line the pipe well let's replace all and we want to continue searching from the beginning yeah well now that we have the last of the record figured out let's find one paragraph Mark and replace it with a tab so I'm going to come up to find what and just leave in one paragraph mark take out that pipe come down to special and replace it with
a tab and that tab is what is going to make our records go across and then we'll do a first find and replace so if found it down here in the middle of our list which is fine and we'll replace and notice how now things are starting to go across but we'll just do a replace all it's done a lot of Replacements we want to start at the beginning and do even more and we'll say okay and I know this kind of looks like one big mess but now we have that flow of the records to make them go horizontally acro
ss the page so what we need to do is find our pipe and replace it with a paragraph Mark so we're kind of reversing what we did earlier let me pull this up a little bit go to special because paragraph Mark is going to give us the end of our record and let's replace all made five Replacements let's see what we have oh and then all done we made nine so let's see what we have I'm going to close out of my find replace window now that we have our information pretty much horizontal instead of vertical
first of all I see that address list has two tabs just because generally in typing we would press enter to go down one line and then to have a blank line we would press enter a second time so I'm going to click here in front of Ray Parker and move him down now from here I'm going to select all of the data and then just so that I can see and make sure that the columns are going to line up I'm going to go on the Home tab below clipboard I'm going to find the tab button which right now has an L on
it showing that we're going to make left tabs and so I'm going to add in a two inch tab and then I'm going to add in a five and a quarter inch Tab and a seven inch tab and then we're just going to go with seven and a half now as I look at this I see that this person here is a little bit off and it looks like maybe there was an extra space between the first name and the last name causing a tab in here so I'm going to backspace so that the full name is all together and then because I've done that
these treat and the city and the state and the zip all line up when I decide to save I have two different options one is to go to file and just save and it's going to show me that some of the features because we're using plain text may not quite work out right and so it's just a general warning that we're not saving this as a word file and so we could save it by saying yes now what we're going to do is close out though and go back so I'm going to cancel here we're going to go back to file and sa
ve as we're going to save it as address list original Three make sure we have plain text and then at the lower right click on Save and then we may get a question about the file conversion but we'll just say okay and we'll close out from here we'll go to excel and kind of reverse what we've done in the past so we'll go to open we'll browse now we'll go to our desktop and find the text file that we created so in the lower right instead of all Excel files we need to click on text files and that giv
es us a nice list on my desktop of address list original Three and we'll open it now at this point we get a three-step text import wizard which is used from time to time it's a really great tool and we have first of all delimited text because we used tabs we could also use commas and several other different kinds of ways of separating our text out but we'll say delimited be sure to go with my data has headers if that's the case we'll click next and then it's already picked up that we have a tab
delimiter but notice that we could also have used semicolons commas spaces spaces are a little tricky though and then we get a data previewed well let's click on next and then we can go step by step through each column and Define how that data is going to work so we could say well it's just general and so General converts numbers to numbers it converts dates to dates and all other values to text which it's going to cover a lot of different issues sometimes things that look like numbers are actua
lly just text for example a zip code you don't really do math on a zip code so it is essentially text and then you can Define dates or you can even decide not to import some of the information that's coming in well we're just going to take the defaults and finish and when we do now we still get that message about we might have some lost data because we're bringing it into Excel and I find that yellow bar kind of annoying so we're going to go over to the far right and click on the X to turn that
off but all I'm going to do then is select all of those columns that have data in them come over to the Sales Group which is to the right of middle select format and then autofit the column width and look at that don't we have a beautiful horizontal address list now the only thing is we want to be sure to do a file save as and I may even browse here and this we may call something like address list instead of original Three we could even call it final now notice that it's defaulted to tab delimit
ed text so I want to come over to the drop down and save it as an Excel workbook and then click save oh my goodness look at this it looks like Excel and when I close out and I look on my desktop and let me bring it down so we can find it here is the address list Final and it has the Excel icon to it when we double click it then it opens right up and this is what we were hoping for from the beginning to learn even more be sure to go to mymcpl.org forward slash online learning for such great resou
rces as universal class Learning Express Library LinkedIn learning and udemy business we also premiered videos every Wednesday and Friday at one o'clock if you miss a video premiere you'll still be able to find it later when it's convenient for you you can find us on Facebook at mcpl 360 and you can find us on YouTube by going to our mcplmo Channel
Comments