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

Excel function tutorials, Excel spreadsheets (.xls)

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:

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

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

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

gardena r40li pris

Finding a running partner could be a great solution to help you develop better
habits of running more often. While functionality is the number one goal of every home appliance it does
not hurt to look good either. You should have a healthy

legal

This piece of writing is really a nice one it assists new web viewers, who are wishing for blogging.

今日発表し、別の1つのバージョンの遅いが、拡張型ブロックを非常に有限会社、オメガスーパーコピー1線独特の腕時計、豚の特徴A制作の彫刻は、ウィラード維岡。一種の ロバート件1は一

[url=http://www.wtobrand.com/lvc8.html]今日発表し、別の1つのバージョンの遅いが、拡張型ブロックを非常に有限会社、オメガスーパーコピー1線独特の腕時計、豚の特徴A制作の彫刻は、ウィラード維岡。一種の ロバート件1は一番新しく、添加していない以下のブロックは、可視模型特色Aヨット、A A Aハチドリ、ファルコンと甚だしきに至っては部落マスク。[/url]