# Tag Archives: Array formula

## Extract FirstLast names in CamelCase with #Excel formula

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

## 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 , , , , | 3 Comments

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

## 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 | | 6 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 , , , , , , | 3 Comments

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

## 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 | | 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 | | 5 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 | | 10 Comments