All articles with 'find' Tag

Are you in IT & use Excel often? This article explains top 10 formulas for IT professionals. Useful for project managers, IT analysts, Testing people and BAs.

We cover a 10 practical situations and explore various Excel formulas to solve them. Example workbook provides more details too.

Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.

I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said ministry of MAGIC!!! ). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”

I explained him how to do this while chewing mouthfuls of rice & veggies.

But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.

So here we go.

Here is a quick homework to keep you busy this weekend.

Can you extract number of days from below text.

Nov15 PUTS (23 days)
March15 TIKS (3 days)
March1 TIKS (25 days)
June11 TIKS (10 days)

Assume the data is from cell A1.

Your solution should return the following:
23
3
25
10

Post your answers (formulas, VBA code or Power Query M code) in the comments.

Use Excel to extract matching words from a list of words from a cell containing a sentence into individual cells in the order they exist

Here is an interesting question someone asked me recently,

If I have to delete all rows with “John” in it. Do you know how to do it?

Well, it looks like they really hate John. But it is none of my business.

So lets go ahead and understand a dead-simple way to get rid of all cells with John or whoever else you fancy.

Today in Formula Forensics we look at how to strip away leading zeroes from strings with this simple array formula

Often we deal with data where numbers are buried inside text and we need to extract them. Today morning I had such task. As you know, we recently ran a survey asking how much salary you make. We had 1800 responses to it so far. I took the data to Excel to analyze it. And surprise! the numbers are a mess. Here is a sample of the data.

No, Not that Slash !

How do I find the 4th Slash in a text string?
Today at Formula Forensics we take a look at 4 different methods.

Most of you know that during day time I work as a business analyst. Today while preparing some test scenarios for our latest insurance application, I came across a weird problem. There are some steps in testing. For each test scenario, a combination of these steps is required. It is my responsibility to identify the […]

Today we will learn to use Excel’s LEFT and FIND formulas. But what fun it is to learn a new formula on a Tuesday? So, we will actually learn to use these formulas to solve the problem: “extract the username from an email ID” How is an email ID structured? Any email ID contains 2 […]

Excel is very good for keeping track of your investments. Due to its grid nature, you can easily create a table of all the mutual fund holdings and monitor the latest NAVs (Net Asset Values) to see how your investments are doing. A while back we have posted a file on tracking mutual funds using excel. Today we are going to release an upgrade for that file.

Read the rest of this post to understand how this template works and download the free template.

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

In today’s installment of “your week @ PHD” we will learn how to use array formulas to refine text search, a bunch of very useful keyboard shortcuts, and a very simple tip on how to get ready when you want to make a chart. All really juicy tips, so read on.

Many of us use spreadsheets to manage huge lists of data, like customer data bases, salesperson data bases etc. Today we will learn a little conditional formatting trick that you can use to search a worksheet full of data and highlight the matching cells.