Tag Archives: SUBSTITUTE

How to remove leading space in #Excel

Posted on May 10, 2018 by MF

When TRIM and CLEAN do not work… Is it something bothering you? To fix this problem, we need to understand where is the “leading space” coming come.  Before we jump to that, I want to show you two Excel functions … Continue reading →

Rate this:

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

Text manipulation with Excel functions

Posted on July 23, 2016 by MF

We talked about RIGHT, LEFT, MID, UPPER, LOWER, PROPER, FIND, SEARCH, REPLACE and SUBSTITUTE in the past few weeks.  Now it’s time to put every thing together to solve a problem.  This is actually the most amazing part of using … Continue reading →

Rate this:

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

Case-insensitive SUBSTITUTE???…

Posted on July 16, 2016 by MF

We talked about REPLACE and SUBSTITUTE last week.  At the final note, I said that SUBSTITUTE is case-sensitive.  If we need to perform a case-insensitive SUBSTITUTE, it is indeed not an easy job unless we are talking a single letter … Continue reading →

Rate this:

Posted in Formula | Tagged , , | 9 Comments

REPLACE vs. SUBSTITUTE in Excel

Posted on July 9, 2016 by MF

Another pair of functions that has very similar meaning literally.   Although I am not able to tell you the differences between “replace” and “substitute” in English, I can show you the differences of the REPLACE and SUBSTITUTE in Excel. … 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

Extract last word from a text string

Posted on November 17, 2014 by MF

There are many cases that we may just want the last word from a text string.  Typical example would be something like the last row in the above screenshot: There are many codes at the end of a text string … Continue reading →

Rate this:

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

Advanced vlookup – wildcard characters “?” and “*”

Posted on January 12, 2014 by MF

Why vlookup gives me wrong answer??? Bugs in vlookup???  I am sure it’s not about text vs. number, but it still fails to give the correct answer!!! I guess many users are not aware of the wildcard characters in Excel.  … Continue reading →

Rate this:

Posted in Formula | Tagged , , | 27 Comments