how to sort addresses in excel This is a topic that many people are looking for. thetruthaboutdow.org is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, thetruthaboutdow.org would like to introduce to you Use Excel and Text Functions to Extract Street Names and Cities from Addresses . Following along are instructions in the video below:
“This is ralph and in this video. And i go over the solution to a a recent quiz. You had requiring use of some text functions in excel. You re with some mock data.
And you ve got some very descriptive street addresses and you ve got some city state combinations and let s see i m challenging you to give me only the street. The house numbers and then the cities. The commas and the states okay so let s see in the directions for this one use a function to display only the street of the address for example pine street elm street oak street etc. Make sure.
The house numbers have varying links like my example. So in this one. I challenged you you had to create your own mock data. It would have been too easy of a problem if all of your house numbers were like three digits or all four digits or all two digits.
Because then you could have just used a amid function starting at a very known point. But now we have an unknown point so let s tackle this let me zoom in a bit more here. So my goal is simply just to get the houses. I m sorry just to get the streets pine street elm street and not get the house numbers well.
I m looking for that common piece of information that they all have and there s always a space in between the house number in the street. So i m going to look for a space. So just so you know i could simply do equals find. I m going to find a space within the address.
Let s see what that does for me good information. Here. So i now know that for this first address that i ve got the space is at position 4 three digit number there s position 4 for the space. Here we go from my next street.
The spaces at position 5 1. 2. 3. 4.
4. The number spaces at five fantastic information. Because that means if i can find the space. And i can go one character after the space that must mean.
It s the start of the address the street easy enough to do so now that i know the value in the what the result of a fine function is the result of a fine function is simply numeric value. Let s modify this a bit now i m going to use a mid function and the purpose. The mid function is to display characters in a text string starting from some middle point. The text string in question is the cell that contains the complete address comma.
What s the starting position that i want well the starting position is one character after the location of the space. So you remember that function. I just used to find the space find a space within the street address. Now.
I know that finding the space. In this first address is going to give me a four position four. But my street name doesn t start until position five. So i need to add one to that result and that ll get me a five so finding the space in my first address gives me a four.
But if i add one to that that will give me a five which means i can start displaying there now my mid function has a third parameter. The number of characters that i want and there s some simple ways in complex ways you could solve this one. I m going to go for simple first simple is i just put a number down that i know is going to be bigger than any street address so i m putting down 15 characters hopefully. I don t have any streets of more than 15 characters close that parenthesis off finish my function press enter.
There s pine street. Autofill down. Excellent. So this actually gets the problem solved for the quiz would have been perfectly solvable and that by the way putting that big 15.
There or 20 or 99. Whatever you do it s not going to hurt anything okay. Doesn t really cause us any problems at all so that s a perfectly fine solution. The more complicated solution that i don t know if i ll demo here.
But but basically is if i want to be really precise i could take the length of the complete street address and then i could subtract the location of where the space is okay. So let s check this out what the hell. I guess i m doing it anyway right so i ll just take this find of the space pop that in there and you ll notice. I m getting the same result.
But it required obviously a lot more effort on my part because now i say well i got varying lengths of information for those for the street names actually are pretty similar. But they re different so now i m taking the length of the whole thing and i m subtracting the location of the space. So a different way of going i don t want to say. One is better than the other because both got me accurate results for what i needed the other part of this problem was um displaying the city only and you know what that might be easier you know if you got the street.
Only then doing city. Only is not so bad except with city like city only we can start from the left instead of the midpoint so start from the left text. In question is the cell that contains the complete city state comma and then the left function is asking me to provide the number of characters. So the number of characters is going to be 1 before the location of the comma.
The comma is what they all have in common so let me find the comma real quick find the comma within. The city state combination and then close that off so my left function will start from the left of b2. And it ll stop once it finds the comma let s see what happens there we go so i have my cities only but it included that comma so i have to make i have to go one before the comma. So i just go to my one of my functions.
So i know that my find function tells me where the comma is but if i subtract one. I can go to the left of the comma there we go city only yeah. So that s yeah that works out pretty well ” ..
Thank you for watching all the articles on the topic Use Excel and Text Functions to Extract Street Names and Cities from Addresses . All shares of thetruthaboutdow.org are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
“Use a combination of functions like left(), mid(), and find() to display only the street names and cities from provided address information.nnThis is a solution to a CIS 235 quiz related to text functions.”,
235 quiz solution, cis, 235, solution, excel, left, mid, find, text, function, functions