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

Excel function tutorials, Excel spreadsheets (.xls)

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to for professional help and Excel Tutorials

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:


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:


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


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


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:


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


4 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
breakfast because you will require energy to carry out your tasks.


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

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

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

ますます多くの西洋の販売人員を意識し始め、英語やフランス語と日本語の間の言語障害ではない。ブレゲ-スーパーコピー しかし、それは最初からミスの腕時計ブランドにとって、影響は

[url=]ますます多くの西洋の販売人員を意識し始め、英語やフランス語と日本語の間の言語障害ではない。ブレゲ-スーパーコピー しかし、それは最初からミスの腕時計ブランドにとって、影響は巨大な。中国では、お客様に覚えてあなたの洋式の名称が難しい。一方、一度名前を覚えて、なかなか忘れられなくても、公式の名称。例えば、Hublot年以上前に自分の中国語の名前に「ウブロ」(Heng Bao)から「宇船」(Yuけん)は、今は50%以上の検索を使ってのはその旧名。この場合の結果よりもあなたの想像以上にひどい:これは基本的に意味がこのブランド検索エンジンマーケティングの倍の投入でネット上でその人を見つけることができる。[/url]


Please Leave a Comment!

Please note: Comments may be moderated. It may take a while for them to show on the page.