Search

# All articles with 'wildcards in excel' Tag

## Finding if a cell has 7 in it… [Pattern matching in Excel]

Published on Oct 18, 2016 in Learn Excel

Imagine you work at MI5 as a HR officer. You want to find all agents who have license to kill (licence 7). Your data looks like above.

How would you go about it?

If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or not7. So how would you solve this problem?

Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic.

Continue »

## Not so wild lookups [video]

Published on Feb 12, 2016 in Excel Howtos, Learn Excel

In case, this is the first time you are hearing about Excel formula wildcards, check out the Using wildcards in Excel VLOOKUP formula tutorial.

So you know about wild cards like * ?, now how would you tell VLOOKUP to ignore them?

Say, you are genuinely interested in looking the value “* Payroll” in a lookup table. What then?

This is exactly the problem faced by Peter in our forum post VLOOKUP and cells with “*” NOT to be interpreted as wildcard

Continue »

## CP026: All about Excel !@#\$%^+/*(}][<

Published on Dec 4, 2014 in Chandoo.org Podcast Sessions

In the 26th session of Chandoo.org podcast, let’s learn all about Excel !@#\$%^+/*(}][<.

I am talking about Excel operators, you silly.

What is in this session?

Do you know Excel has more than 25 operators? That is right. There are a variety of operators beyond the simple + – * and /.

In this podcast, let’s understand all about these operators and how to use them. You will learn,

• Why there is a gap between last & this podcast session
• Arithmetic operators
• Text operators
• Reference operators
• Comparison operators
• Closing thoughts
Continue »

## Find first non-blank item in a list with formulas

Published on Jan 15, 2014 in Excel Challenges, Excel Howtos

Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, time consuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:

Today, lets talk about one more scenario. Lets say you want to find out the first non-blank item in a list. How would you do it?

Continue »

## Countif – Quick Tip

Published on Dec 16, 2013 in Huis, Posts by Hui, Quick Tip

Learn how to Count Text, Blanks and Non-Blank cells with this Countif() Quick Tip

Continue »

## Can you find that pattern? [Homework]

Published on Dec 14, 2012 in Excel Challenges

Are you ready for an Excel challenge?

Today, your job is very simple. Just find a pattern in a text and return corresponding value.

In a range we have some resource types & their billing rates.

In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.

See above diagram.

Continue »

## Even faster ways to Extract file name from path [quick tip]

Published on Oct 24, 2012 in Excel Howtos, VBA Macros

The best thing about Excel is that you can do the same thing in several ways. Our yesterdays problem – Extracting file name from full path is no different. There are many different ways to do it, apart from writing a formula. Learn these techniques to be a data extraction ninja.

1. Using find replace
2. Using text to columns
3. Using UDFs

Continue »

## Making VLOOKUP formulas go wild [VLOOKUP Week]

Published on Nov 1, 2010 in Learn Excel

Situation

Often we need our lookup formulas to go wild. Not in the sense of go-wild-and-chomp-a-few-kilo-bytes-of-data sense. But wild like wild cards. For eg. In the below data, we may not remember the full name of sales person, but we know that her name starts with jac. Now how do you get the sales amount for that person?

Read more to find how to solve this.

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.