Tag Archives: Array formula

Extract FirstLast names in CamelCase with #Excel formula

Posted on March 10, 2019 by MF

In the previous blog post, we see how Flash Fill extracts First Name and Last Name from an email address in a format shown above.  I’ve also recorded a video for that post. Flash Fill is so smart to detect … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , , | Leave a comment

Lookup a specific combination of characters exclusively, e.g. 5A not 5AA

Posted on September 10, 2017 by MF

Got the following question: hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , | 3 Comments

Transpose Data (Static and Linked)

Posted on January 14, 2017 by MF

Answer to the 5 little Tricks posted in the beginning of the year – Part 1/5 Transpose Data – Result is static This is actually a simple trick of using Copy and Paster (Special, Transpose).  See? It can be done in a … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , , , , | 2 Comments

Number Stored as Text…

Posted on May 14, 2016 by MF

It is something basic and commonly seen.  Nonetheless,  it is really important to understand the different behaviors between a number, and a number stored as text, especially if you want to move forward to write effective formula.  This post is intended … Continue reading →

Rate this:

Posted in Excel Basic, Formula | Tagged , , | 6 Comments

Three different ways to do case-sensitive lookup

Posted on April 9, 2016 by MF

If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is case-insensitive.  It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , , | 3 Comments

Make impossible possible

Posted on February 20, 2016 by MF

Convert text of specific pattern like “1d 2h 3m 4s” into real time (26:03:04) in Excel To kick start the Year of Monkey, let’s challenge the apparently impossible… Do you think it is not possible in Excel?  If you do, you … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , , | 2 Comments

SUM Time in the end of text strings

Posted on July 25, 2015 by MF

Did you encounter a task like this?  I did.  Actually it is not as difficult as you may expect.  It could be done in just a minute IF the data is not too bad, like the one shown above. I … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , , , , , , , | 6 Comments

Sum a range of number end with “k”

Posted on April 11, 2015 by MF

Does it sound familiar to you? If you are having this kind of issue all the time, you may want to continue reading this as I am going to show you three different approaches to fix the problem. Non-Formula approach Formula … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , , , | 5 Comments

15-significant-digit issue with SUMIF(S), COUNTIF(S), AVERAGEIF(S)

Posted on January 24, 2015 by MF

Have you applied SUMIF or COUNTIF to a range that contains numbers with more than 15 digits stored as text? If you did or if you do, you’d better continue to read this as you’ve got wrong answers that you … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , | 10 Comments