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

HUGE LIMITED TIME DISCOUNT ON THE BEST ADVANCED EXCEL COURSE ENROLL NOW!!
excel_look_professional
Making Excel Worksheets Look Professional – A Case Study
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
FREEZE PANES
Freeze Panes in Excel – The Complete Guide
HOW TO LEARN EXCEL
How to Learn Excel and Be a Spreadsheet Champion
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
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
Pivot Tables
How To Convert Pivot Table Into Frequency Distribution
How to Convert Pivot Table into a Frequency Distribution in Excel

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.

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