200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Part Description
Solar Glass (Low Iron Tempered), 34" x 96"
Many of the part descriptions are too long, so to get a calculationthe first 8 characters of the part description and then copy the formula down. That's not ideal, so we want to change it to get all the text to the left of the first comma. Which function will allow us to locate the position of the first comma?
The formula you are asking about is this one.
=LEFT(A2,FIND(",",A2)-1)The original text is in A2. However, if there is no comma in A2 an error will occur. Therefore the formula should be embedded in an IFERROR() function which will return the unchanged string in case of error.
=IFERROR(LEFT(A2,FIND(",",A2)-1),A2)Don't use this formula, if you want the error to occur so that someone's attention is drawn to look for its reason and perhaps fix it.
There is a new way (well, 2013, I believe) of achieving the same result parsing the XML. The formula below will return the first element of an array created by splitting the value in A2 on commas.
=FILTERXML("<data><a>"&SUBSTITUTE(A2,",","</a><a>")&"</a></data>","/data/a[1]")It's the final 1 that specifies the first element of the array. The good thing is that you can also extract the 2nd and subsequent elements by just changing the element number at the end of the formula. Bear in mind that the first character of the second element should be the blank that follows the comma. Therefore you should embed the formula in a TRIM() function to remove it.
=TRIM(FILTERXML("<data><a>"&SUBSTITUTE(A2,",","</a><a>")&"</a></data>","/data/a[2]"))No error will occur if there is no comma in A2. The first element will just be larger. But there will be an error if the specified element is greater than the number of elements in the parsed array, such as would also be the case if A2 is blank. So you might still embed the formula in an ISERROR() function returning different things depending upon whether A2 is blank or doesn't have enough commas.