Extracting The First Word From A Text String
Hi there. It’s a lovely winter evening here in India as I sit down to write this post.
Today I am in the mood to discuss something really interesting. Suppose you have a column of text strings and you want to extract the first word of all of those text strings. There is no function in excel which you can use directly for this purpose. You need to build a formula for this, and that is what we are going to discuss in this post.
The formula that we are going to use is
(Cell A2 is assumed to have the text string in question)
We have used 3 different functions in this formula. Below are the links which can help you understand these functions better in case you are not aware of these.
Click here to learn about FIND function.
Click here to learn about LEFT function.
Click here to learn about IFERROR function.
What role of FIND function in this formula is to find the position of the first space in the text string. The minus 1 will deduct 1 from the position.
The LEFT function will then find this position in the text string and extract that number of characters, which will give the first word in the text string as the result.
The formula with the functions LEFT and FIND is sufficient in cases where the text string has more than one word. But this formula would not work if the text string has just one word and will give an error. So, to make sure our formula works well in every situation, what we need is to insert the IFERROR function. What this function does is it gives the result given in the formula if there is an error in the formula. So if there is only one word in the text string, using the IFERROR function and giving the desired result as the text string itself in case of an error, will give the single word text string itself as the result.
Now you just need to pull down the formula to get the first words for all the text strings in a particular column.
I highly recommend you watch the above video to get a better understanding of this formula. There you will watch me explain the whole process with the help of an example which will help you understand the concept better.
I would love to hear your thoughts about this post and also about MadAboutExcel as a whole. Please post your comments below the post.
To subscribe to MadAboutExcel newsletter, just fill in the opt-in form right below this post.
See you soon. Happy Excelling!!
GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE
Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!
Thank you for subscribing.
Something went wrong.