which trims " miles" from each value returning just the number. The VALUE function converts text to a numeric value. The challenge, for values with variable length, is that we don’t know exactly how many characters to extract. That’s where the LEN function is used. Working from the inside out, LEN calculates the total length of each value. For D6 (736 miles) the total length is 9. To get the number of characters to extract, we subtract 6, which the length of " miles", including the space character. The result is 3, which is fed to LEFT as the number of characters to extract. LEFT then returns the text “736” as a text value. Finally, because we want a numeric value (and not text) we run the text through the VALUE function, which converts numbers in text format to actual numbers. The formula steps look like this: Note: there is no reason to use the VALUE function if you don’t need a numeric result.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
