Tag Archives: Array formula

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

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 , , , , | 1 Comment

Transpose Data (Static and Linked)

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 , , , , | Leave a comment

Number Stored as Text…

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 , , | 5 Comments

Three different ways to do case-sensitive lookup

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 , , , , , , | Leave a comment

Make impossible possible

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 , , , , , | Leave a comment

SUM Time in the end of text strings

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”

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 , , , | 2 Comments

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

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 , , , , | Leave a comment