Separating Data In Excel – A Real Case Using Addresses (Part 2 – Managing Exceptions)

In my previous post, I started off from one column and used different functions such as left, right and mid to separate my data into columns that will be easier to work with. when that is done, you will be able to look through all of the data and see how things look, what kind of exceptions came in, etc.

One data that happened in my case is that I could have a few cities where the data had a neighborhood that was specified. Just take a look:

y

This might be something that happens all over my spreadsheet and could cause different issues. I only want the exact city name to appear because that is what I’d use for stats, to send out mail, etc. How can I get rid of it? There are 2 main options:

#1-Adjusting the initial formula (depending on what the type of error is, this could certainly work)

#2-Adding Exception Management to that formula: This is the road I will take here and while it might *look messy, it will be done fairly easily. So as you know, my current formula is:

=MID(A2,B2+3,C2-B2-3)

I first want to know if there is a “(” in that formula. I will use the “find” function here:

=FIND(“(“,MID(A2,B2+3,C2-B2-3))

Here, I either get a number or an error. I will then simply manage that error by adding an “if” function:

=IF(ISERROR(FIND(“(“,MID(A2,B2+3,C2-B2-3))),0,1)

Basically, if there is an error, it means no adjustment is needed so I will replace the 0 in that formula by what I had.

However, if I do not get an error, I will adjust that answer by only capturing what is left of that “(“:

So I would get the city only:

=IF(ISERROR(FIND(“(“,MID(A2,B2+3,C2-B2-3))),MID(A2,B2+3,C2-B2-3),LEFT(MID(A2,B2+3,C2-B2-3),FIND(“(“,MID(A2,B2+3,C2-B2-3))-1))

I know it looks messy but take the time to look at the formula. It resolves my issue and would “clean” up my data for all such cases.

Here is the new result:

You can also download the spreadsheet

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

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

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