If the string doesnt always end with 8 characters that you want to strip off. SUBSTITUTE (LEFT (A2,LEN (A2)-8),'','-') This will achieve the same resulting string. This assumes that the changing data on the end is always present and is 8 characters long. And if/when they change you have to go back to every formula in which the variable has been hard-coded and make that change. Another way to do it is by utilizing Left and Right functions. Excel,Excel,Excel for Mac,Excel for Mac,Excel for Windows Phone,Excel Online,Excel Starter,Excel Web App. The Excel IF function statement allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False. And variables, by definition, are subject to change. Multiple IF functions can be nested together to allow for multiple criteria.
(2) Second observation: by incorporating the values 0.1 and 0.05 directly in your formulas, you are doing what's referred to as 'hard-coding' what is in fact a variable.
The net result of what you'll learn is that you should be using a formula like this in Cell H31, and then it can be copied as is to all the other appropriate cells the absolute reference to $C$26 will stay as is, and the others will change. Here's a screen grab of the first part of what you'll read: So suggestion #1: look into absolute and relative references in Excel Help. (1) Look at your cell H31: it reads as follows (unless you've fixed it in the meantime):