All articles with 'find' Tag

Find them and Extract them – VBA Macro

Published on Feb 10, 2017 in VBA Macros
Find them and Extract them – VBA Macro

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

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

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
Formula Forensics No. 34. Extract words from a cell, where they occur in a list of words.

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
How to remove all cells containing John (or anything else) [Quick tip]

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
Formula Forensics No. 027 – Remove Leading Zeroes

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
Extracting numbers from text in excel [Case study]

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
Formula Forensic No. 021 – Find the 4th Slash !

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
Find and Remove Blank Items from a Range of Cells [personal experience]

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
Extract usernames from E-mail IDs [using LEFT and FIND formulas in 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
Track Your Mutual Fund Portfolio using Excel [India Only]

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
Search a Spreadsheet Full of Data using Conditional Formatting

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 »

What are the formulas you wish Excel had ?

Published on Jan 13, 2009 in Featured, Learn Excel

Here is a list of formulas I wish MS Excel had. Alas, we need to build some work-arounds to solve them though.

Continue »

Weekly Excel Links – Issues with Hosting of PHD

Published on Jan 6, 2009 in excel links

Over the weekend I got thinking about the various ways in which PHD can help you and I wonder if PHD should have forums. For one, forums are a great way to discuss ideas, find answers and develop community feeling. But at the same time, there are tons of very good excel forums that having another one may not really help. Currently I am leaning towards starting a forum to see how it goes. What do you think I should do ?

Continue »