On this website, we use many different types of functions but it’s clearly important to also use real life examples when trying to use functions. Recently, I was asked by a reader to help out with a task that I assume many others are trying to do. When you have a type of data such as:
And you need to reclassify this into different columns, it can be a challenge. I could not use the addresses from that person’s file (obviously) so I got a few of my own and decided to play around with these.
Suppose I had that list and needed to get the address into different columns (there could be many different reasons for doing this). How would I do it? Imagine that I had 1000 or 10000 such lines? I’ll give an example that will hopefully give you an idea how.
Step #1 – The most critical is step (by far) is understanding how the data is presented.
In this case I have the street address followed by ” · “, then the city, followed by “,”, the postcal code and then a space and the country. I can work with this. In some cases, the data will not be as uniform which provides more challenges but can still be done.
Step #2 – Find those “markers”. I’ll start by looking for ” · “. How? The find function:
=FIND(” · “,A2)
Then, I’ll find the next one:
=FIND(“,”,A2,B2)
From there I should be able to proceed:
Step #3 –
Street address would be:
=LEFT(A2,B2-1)
I would then use the “mid” for the city:
=MID(A2,B2+3,C2-B2-3)
and the same for the postal code:
=MID(A2,C2+2,7)
Finally, I could use that same function for the country by assuming the postal code always has 7 characters:
=MID(A2,C2+10,20)
The result?
You can download the spreadsheet here.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
