For instance, the formula =SEARCH(A1, B1) will look for the content of cell B1 within cell A1 and then return a number, indicating the position where the string in B1 begins in cell A1. The SEARCH function looks for a string inside another string and then returns the position of the starting character. Related: How to Count Words and Characters in Excel With the LEN Function We're going to use two handy functions and have them do the indicating and counting for us: SEARCH, and LEN. Luckily, this is Excel we're talking about and everything can be automated. First, type the equal sign followed by the range of cells you want to subtract from in the SUM function, then type the minus sign and the cell reference that needs to be subtracted. This sounds like a lot of work because if you were to count the starting character and the total amount of characters you want to subtract, you could just do the subtraction manually instead of using this formula. Although Excel has no subtract function, you can subtract from a range of cells using the SUM function. However, the REPLACE function is more precise as you have to feed it the exact position of the characters you want to replace.įirst, you have to indicate the position of the first character, then you have to input into the REPLACE function how many characters from that point on it should replace with the new string.

Much like the SUBSTITUTE function, the REPLACE function finds a target string and then replaces it with a given string. Hence, write down the below formula in the Formula Bar. = REPLACE(old_text, start_num, num_chars, new_text) Step 1: First, select cell E5 to subtract military time.

Subtraction formula in excel