This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins.
The formula is a bit convoluted, but the steps are simple. We first count how many spaces exist in the text using LEN and SUBSTITUTE. Next, we use the somewhat obscure "instance" argument in the SUBSTITUTE function to replace the last space with a tilde (~). Finally, we use FIND to figure out where the tilde is, and the MID function to discard everything after the tilde.
Working from the inside-out, we use the LEN and SUBSTITUTE functions to count how many spaces appear in the next:
The replace function lets you replace text based on its location and length. In this case, we want to strip off the drive and path, and leave only the document name.The length of this part of the string (text) is 24 and the starting position is 1,...
The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the "new_text" argument. In this case, we want to remove the labels that appear inside text. The labels vary in length, and...
The LEFT function is perfect for extracting characters starting from the left side of a text string. We use LEFT in this formula to extract all characters up to the number of characters we want to trim. The challenge, for values with variable length...
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.