Excel/Extracting from a string
QUESTION: Hi Tom
In cell A3, I would like to extract the first word from a string in cell D6. For example, in cell D6, the string is:
MAPLE B2874 (INT)
and I would like the word "MAPLE" to appear in cell A3. I cant use =Left(D6,5), because the string in cell D6 changes, and the next time the first word may not be 5 characters. Is there a formula I could put in Cell A3 that would find the (variable) first word each time?
You can search for the first space in the string to use as the second argument to the left function:
A3: =LEFT(D6,FIND(" ",D6)-1)
if it could sometimes could be a single word you could do
A3: =LEFT(D6,FIND(" ",D6&" ")-1)
since Find will return an error value if the target value isn't found. This avoids that for a single word entry and still work for an entry with more than one word.
---------- FOLLOW-UP ----------
QUESTION: Thanks Tom, that works great. Could I just ask what purpose the -1 serves? I have noticed it quite a lot in the formulas that you build for me, but dont know what it does!
in your example string, MAPLE B2874 (INT)
the find(" ",D6) would return 6 since the space occurs in the 6th position.
But we only want the letters before that space (to the left of the space), so we subtract 1 from the results of the FIND function to get the 5 for the second argument of the LEFT function.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here