How To Extract The First Word From A Text String

Published on January 17, 2017
image_pdfimage_print

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.First Word

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

=IFERROR(LEFT(A2,FIND(” “,A2)-1),A2)

(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 ULTIMATE EXCEL SHORTCUTS COURSE
worth $40 for FREE!!

Subscribe to our newsletter and get interesting stuff and updates to your email inbox.

Thank you for subscribing.

Something went wrong.

Huge Discount on the Best ADVANCED EXCEL Course. GET IT NOW!! CLICK HERE!!
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
BECOME AN EXCEL NINJA COMPLETE REVIEW
Become An Excel Ninja – Course Review
BEST EXCEL COURSES
10 Best Online Courses to Make You an Excel Wizard
HOW TO GET MY $40 COURSE FOR FREE
Get My $40 Excel Course For Free!! COUPON CODE Inside!!
14 Excel tips
14 Excel Tips – Make Yourself Super-Efficient in Excel
DATA CLEANING EXCEL
Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques
RANDOMIZE LIST RANDBETWEEN
Randomize a List in Excel Using RANDBETWEEN Function
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
How to Sort Excel Worksheet in Alphabetical Order
How to Sort Excel Worksheets in Alphabetical Order
VBAPOST1
How to Add And Name A New Sheet At The Same Time Using Excel VBA
ROUNDING NUMBERS IN EXCEL
Rounding Numbers in Excel – The Complete Guide
number_of_words
How to Find the Number of Words in a Text String in Excel
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
How To Indentify ULP case in a text string
How To Identify If A Text String Is In Upper, Lower Or Proper Case
How To Change Shape of Comment Box in Excel
How To Change The Shape Of Comment Box In Excel
Excel Dumbest Challenge
Excel Dumb Challenge – Do You Have Time To Waste?

Get my ULTIMATE EXCEL SHORTCUTS COURSE
worth $40 for FREE!!

Thank you for subscribing.

Something went wrong.

Share
Tweet
+1
Pin
Share
Stumble
Enjoyed this video?
How To EXTRACT FIRST WORD
"No Thanks. Please Close This Box!"