You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Extracting from a string

Advertisement

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?

Thanks

Gail

ANSWER: Gail,

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.

--

Regards,

Tom Ogilvy

---------- 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!

Thanks

Gail

Gail,

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.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thanks for that explanation, very clever! |

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

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.