# Separating Data In Excel – A Real Case Using Addresses

### Excel function tutorials

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 –

=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?  