Search

All articles with 'find' Tag

Top 10 Excel formulas for IT people

Published on Jun 18, 2020 in Learn Excel, Project Management

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.

Continue »

Top 10 Excel Formulas for any situation

Published on Mar 27, 2019 in Learn Excel

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.

Continue »

Find them and Extract them – VBA Macro

Published on Feb 10, 2017 in VBA Macros

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.

Continue »

Can you extract numbers from text – homework

Published on Oct 30, 2015 in Excel Challenges, Learn Excel

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```

Continue »

Formula Forensics No. 34. Extract words from a cell, where they occur in a list of words.

Published on Sep 12, 2013 in Formula Forensics, Huis, Posts by Hui

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

Continue »

How to remove all cells containing John (or anything else) [Quick tip]

Published on Mar 15, 2013 in Excel Howtos

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.

Continue »

Formula Forensics No. 027 – Remove Leading Zeroes

Published on Aug 23, 2012 in Formula Forensics, Huis, Posts by Hui

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

Continue »

Extracting numbers from text in excel [Case study]

Published on Jun 19, 2012 in Excel Howtos

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.

Continue »

Formula Forensic No. 021 – Find the 4th Slash !

Published on May 17, 2012 in Formula Forensics, Huis, Posts by Hui

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.

Continue »

Find and Remove Blank Items from a Range of Cells [personal experience]

Published on Feb 23, 2010 in Excel Howtos

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 […]

Continue »

Extract usernames from E-mail IDs [using LEFT and FIND formulas in Excel]

Published on Jan 19, 2010 in Learn Excel

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 […]

Continue »

Track Your Mutual Fund Portfolio using Excel [India Only]

Published on Dec 28, 2009 in excel apps, personal finance

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.

Continue »

29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]

Published on Aug 24, 2009 in Excel Howtos, Featured, Learn Excel

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.

Continue »

Excel Tips Submitted by You [Part 2]

Published on May 12, 2009 in Charts and Graphs, Learn Excel

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.

Continue »

Search a Spreadsheet Full of Data using Conditional Formatting

Published on Mar 31, 2009 in Learn Excel

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.

Continue »

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.