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 –

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


***************************************************