All articles with 'substitute()' Tag

Find out how many times a value is present in a cell [formulas]

Published on Jul 19, 2016 in Excel Howtos, Learn Excel
Find out how many times a value is present in a cell [formulas]

Here is an interesting problem to start your day.

Let’s say you work as DNA sequencing engineer at The Enterprise. And you just unlocked the sequence that is responsible for all male problems. The early onset of baldness. The sequence code is AAAA. And you want to find out how many times this sequence is found in a sample of DNA strings, in the range B6:B19. Essentially you want the above.

So how do you write the formula?

Continue »

Not so wild lookups [video]

Published on Feb 12, 2016 in Excel Howtos, Learn Excel
Not so wild lookups [video]

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 »

Use NUMBERVALUE() to convert European Number format

Published on Oct 6, 2015 in Excel Howtos, Learn Excel

If you deal with customers or colleagues in Europe, often you may see numbers like this:

  • 1.433.502,50
  • 9.324,00
  • 3,141593

When these numbers are pasted in Excel, they become text, because Excel can’t understand them.

Here is a simple way to convert the European numbers to regular ones.

Use NUMBERVALUE() Function.

Continue »

Export iPhone contacts to Excel using this free template

Published on Dec 2, 2014 in Excel Howtos, Learn Excel, Templates
Export iPhone contacts to Excel using this free template

Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.

After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file.

Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template that can convert VCF data to Excel worksheet.

Continue »

Unlock “Angy Formulas” – an Angry Birds like game hidden in Excel using this trick!!!

Published on Apr 1, 2013 in excel apps
Unlock “Angy Formulas” – an Angry Birds like game hidden in Excel using this trick!!!

Sometimes you think you know something and then suddenly you are surprised. Yesterday was such a moment for me. I have been using Excel for almost a decade now. So naturally I assumed that I know it well. But then yesterday, while doing something I stumbled on a strange screen in Excel that looked like very popular Angry birds game. So I got searching. But there was no mention of it anywhere on net. Then I asked my friend Rollf ‘O’ Pai, who is in Micros0ft Execl team. First he denied such a thing. But we knew each other so well that he could never lie to me. So he confided. He told me what I had suspected for several years.

There is an Angry birds like video game buried in Excel!!! It was meant to be an Easter egg in Excel 2010 (and 2013), but due to backlash from senior management no one ever published the details about it.

So I asked him “How do I unlock it?”. Rollf ‘O’ Pai asked me to never reveal it to anyone and then told me the recipe.

Once I unlocked I could not believe how cool it is!

Read on to understand how to unlock this game.

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 »

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 »

Formula Forensics No. 002 – Joyces Question

Published on Nov 7, 2011 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 002 – Joyces Question

Formula Forensics # 002 – Joyces Question

This is the second post of a new regular weekly series at Chanoo.org, Formula Forensics, where we will examine how a formula works from the inside out with a worked example to help you understand its formation.

Last week we looked at Taruns problem and analysed a formula to solve his problem

This week we look at Joyce’s Problem…

Continue »

People & Websites that helped me in 2010 – A Big, Heartfelt Thank You

Published on Dec 29, 2010 in blogging

2010 has been phenomenally awesome for Chandoo.org.

Obviously, most of this success is due to the fantastic community here. That is you.

I feel really fortunate to have you in our community. You constantly inspire and motivate me to learn and share new things. Thank you very much.

But, apart from the vibrant community here at chandoo.org, I am also thankful to several others. I would like to remember all the kind souls who helped me through-out this year to make more of you awesome in Excel.

Continue »

How to make a Birthday Reminder in Excel ? [Video]

Published on Oct 26, 2010 in Excel Howtos

Learn how to create a birthday reminder worksheet in excel in this video post. You can also download a template to keep track of upcoming birthdays and anniversaries.

Continue »

Convert Text to Sentence Case using Excel Formulas [Quick Tips]

Published on Jun 7, 2010 in Excel Howtos
Convert Text to Sentence Case using Excel Formulas [Quick Tips]

It is almost 3:30 am now, I stayed awake for last 23 hours so that Excel School 2 can be ready for rolling. But that is no excuse for not having a post here. So here it goes. Excel has formulas for converting a bunch of text to UPPER, lower and Proper Cases. But not […]

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 »

Analyzing Search Keywords using Excel : Array Formulas in Real Life

Published on Apr 29, 2009 in Charts and Graphs, Learn Excel
Analyzing Search Keywords using Excel : Array Formulas in Real Life

Jarad asks me in an email “how word frequency can be generated from a range of cells using excel formulas?” This got me thinking and lead to this post, where we learn how to calculate word frequency using array formulas and use it to analyze a bunch of search keywords.

Continue »

Generating Random Phone Numbers

Published on Nov 20, 2008 in Analytics, Learn Excel
Generating Random Phone Numbers

Often when you are writing code or testing an application, it might be helpful to have random phone numbers generated for you. Here is an excel formula that I use: =value(left(substitute(rand(),”0.”,””),10)) try these 2 instead (thanks to John) =RAND()*(9999999999-1000000000)+1000000000 =RANDBETWEEN(1000000000,9999999999) [needs analysis toolpack add-in or excel 2007] Even though it works most of the time […]

Continue »

Excel Formula for Finding Repeated Words in a Cell

Published on Nov 11, 2008 in Learn Excel
Excel Formula for Finding Repeated Words in a Cell

If you ever need to know whether a particular word is repeated in a cell (for eg: a file extension or telephone number or name of the customer) here is how you can find it. =if(lower(cell-with-text)<>SUBSTITUTE(lower(cell-with-text),lower(word-to-check),””,2),”Yes”,”No”) We are using substitute() to replace the second occurance of a particular word and then compare this with original […]

Continue »