Excel Substring: How to Get (Extract) Substring in Excel

-

|

Introduction

An Excel substring is a portion of a text in a cell. For instance, if a cell contains the words “Excel substring”, you may want to extract “Excel”, “substring”, “Excel Sub” or even “substring”.

In this guide, I will show you how to extract texts from within a cell. I will use a combination of Text (LEFT, RIGHT and MID) SUBSTITUTEand SEARCH functions to get substrings from before, end or after a specific character of a string.

How to Get Substring in Excel: Start, Middle or End of a String

You can get a substring from the end, middle or beginning of a string. The following sections covers how to do this.

How to Get Excel Substring from the Start of a String

Excel Substring get substring from the beginning of a string using the LEFT

You can get substrings from the left of a string in Excel using the LEFT Function.

Below is the Syntax of the LEFT Function:

LEFT(text, [num_chars]) 

Text is a (required parameter): This is the text string you want to extract the Excel substring from.
Num_chars parameter is Optional. Characters count (subsrting) you want the LEFT function to extract from the string.

If you do not specify the Num_chars (Characters from left of the string), Excel will get the first character.

To get the first 4 characters from this string, 7OV1-BK8P-QD9Y use the following formula:

=LEFT(A6,4)

The formula above assumes that the string is in cell A6. Here is the result.

Excel Substring LEFT Example

You can also specify the string directly in the formula as shown below

=LEFT("OV71-BKP8-QDY9",4)

It produces the same result as shown below:

How to Get Excel Substring from the Right of a String (RIGHT)

The RIGHT function is used to get substring from the right of a string.

Here is the Syntax:

RIGHT(text,[num_chars]) 

Text (Required parameter): The text string containing the characters you want to extract.
Num_chars (Optional parameter): Characters you want RIGHT to extract right of the string.

Continuing from the previous example, to extract the first 5 characters counting from the right of this string, 7OV1-BK8P-QD9Y use the formula:

=RIGHT(A2,5)
For the above formula, the original string is in cell A2. 5 is the number of characters to extract, counting from right.

The result in Excel:

Excel Substring RIGHT Example

How to Get Excel Substring Within a String (MID)

How to Get Excel Substring Within a String (MID)

There are circumstances you may need to extract excel substring from within a string. You will need the MID function for this.

The Syntax of the MID function is:

MID(text, start_num, num_chars) 

Text (Required): The Excel string with the characters you want to extract.
Start_num (Required): The position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
Num_chars (Required): Specifies the number of characters you want MID to return from text.

As an example, to extract BK8P substring from from 7OV1-BK8P-QD9Y, use this:

=MID(A2,6,4)

This tells Excel to extract 4 characters from cell A2, starting from the 6th character. See the result in Excel.

How to Get Substring in Excel: Before or After a Specific Character

So far, this guide have covered how to extract excel substing from the beginning, within and end of a string. In this section, I will discuss how to extract a substring after a particular character.

How to Get Excel Substring Before a Specific Character

In this example, I will show you how to get all characters before the first hyphen (counting from left) in 7OV1BK8P-QD9Y.

There are 2 steps to complete this task:

  1. Use SEARCH function to determine the position of the character, “-“.
  2. Use LEFT function to extract all characters from the position determined by the SEARCH function

First, I will show how to use SEARCH to determine the position of the character.

Here is the Syntax of the SEARCH function:

SEARCH(find_text,within_text,[start_num])

find_text (Required): This is the text you want to find. In our ongoing example, this will be the hyphen, “-“.
within_text (Required): The text in which you want to search for the value of the find_text argument. This is usually the cell number containing the text. For example, the cell containing 7OV1BK8P-QD9Y.
start_num (Optional): The character number in the within_text argument at which you want to start searching.

To get the position of the first hyphen, “-” in 7OV1-BK8P-QD9Y, use the formula:

=SEARCH("-", A2)

The result is 5 as shown in the result below:

The next step is to use the LEFT function to extract all substrings before “-“. Below is the Syntax:

LEFT(text,(SEARCH(find_text, within_text))-1)

text: is a parameter of FIND. This is the original string (usually the cell containing the text)
find_text: is a parameter of SEARCH. This specifies the character you want to extract all characters before it.
within_text: Still a parameter of SEARCH. Same as text parameter in this instance.
1: The value returned by SEARCH is usually over by 1. Subtracting 1 corrects the result.

And now the formula for our example:

=LEFT(A2,(SEARCH("-", A2))-1)

To make the seemingly complex formula easy to understand, I will break it down into the 2 parts. Then put it together again.

Part 1:

LEFT(A2, num_chars)

Here, LEFT will extract num_chars from the text in cell A2. num_chars is the number of characters from the left of the text to extract.

Part 2

(SEARCH("-", A2))-1

This tells SEARCH to find the position of “-” character from the text in cell A2.

When we put it together, we replace num_chars in LEFT function with (SEARCH(“-“, A2))-1. And here is the combined formula:

=LEFT(A2,(SEARCH("-", A2))-1)

And the result in Excel:

The formula is simply a FIND function that uses the SEARCH to determine the number of characters to extract from.

How to Get Excel Substring After a Specific Character

In this section, I will demonstrate how to extract Excel substring after a specific character. I want to extract all characters after the first “-” in 7OV1-BK8P-QD9Y.

The formula is similar to the example in the last section. The difference is that in this section, I will replace LEFT with RIGHT function. I will also use the within_text parameter in the SEARCH function.

Here is the general syntax for extracting Excel substring after a specific character in a text.

=RIGHT(text ,(SEARCH(find_text, text, within_text ))-1)

text: Is a parameter of the RIGHT function. This is the cell containing the string you wish to extract Excel substring from.
find_text: SEARCH parameter. Specifies the character you wish to extract characters from.
text: The second text is a parameter of SEARCH function. This is the cell containing the string you are extracting from.
within_text: This is used to tell SEARCH from what character to search for find_text. In our example within_text is “-“.

And here is the formula to extract all characters after the first “-” in 7OV1-BK8P-QD9Y

=RIGHT(A2,(SEARCH("-", A2,6))-1)

A2 is the cell with the text. “-” is the character we are extracting after and 6 is the character we are SEARCHING for “-“, from the right of the original string.

Below is the result in excel.

Count How Many Times a Substring Appears in Excel

Count How Many Times Substring Appears in Excel

In the last section of this guide, I will show how to count how many times a substring appears in a text.

The Syntax to count the number of times a specific character appears in string is:

=LEN(text)-LEN(SUBSTITUTE(text, count_text,""))

text: is the main string we want to count from
count_text: is the character we want to count.

In the formula, SUBSTITUTE removes all of the characters being counted in the source text. Then the length of the substring (with the character removed) is subtracted from the length of the original string. The result is the number of times the counted sustring appears in the original string.

For example, to count how many times “-” appears in 7OV1-BK8P-QD9Y, use the formula:

=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))

Breaking it down:

Let’s start with the formula:

=SUBSTITUTE(A2,"-","") 

This removes the character, “-” we want to count from the source string, 7OV1-BK8P-QD9Y. The result is 7OV1BK8PQD9Y as shown below:

Count How Many Times Substring Appears in Excel - SUBSTITUTE removes all of the characters being counted in the source text

Next, LEN function counts the characters left after the text we wish to count has been removed from the original text.

Here is the formula

=LEN(SUBSTITUTE(A2,"-",""))
The formula includes SUBSTITUTE(A2,”-“,””) into the LEN function. Here is the result, 12.

Then, we count the number of characters in the original string, with this formula:

=LEN(A2) 

The original text has 14 characters as shown below:

Finally, subtract LEN(SUBSTITUTE(A2,”-“,””)) from LEN(A2)

=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))

And the final result is 2. See the result in Excel below.

Conclusion

I hope this guide simplified Excel substring for you! If you have any question or comments use the “Leave a Reply” form at the end of the page. Alternatively, share your expererience using Excel substring.

Other Helpful Guides

Additional Resources and References

  • Was this post Helpful?
  • YesNo

LEAVE A REPLY

Please enter your comment!
Please enter your name here

ADVERTISEMENTS

FEATURED POSTS

Best Christian Movies on Netflix

The 10 Best Christian Movies on Netflix

This Itechguide reviews the 10 best christian movies on Netflix. The movies are listed in reverse order – with the 10th movie...
How to Change Orientation in Google Docs

How to Change Orientation in Google Docs

This Itechguide teaches you how to change orientation in Google Docs. The guide covers steps to change orientation in Google Docs from...
How to Delete Google Photos

How to Delete Google Photos

This Itechguide teaches you how to delete Google Photos. The guide offers steps to delete Google Photos from the Google Photos App...
How to Install Windows 10 1909 Update Manually

How to Install Windows 10 1909 Update Manually

This Itechguide teaches you how to install Windows 10 1909 update manually. Windows 10 1909 (otherwise known as Windows 10 November update)...
How to Create a Poll on Facebook

How to Create a Poll on Facebook

This Itechguide teaches you how to create a poll on Facebook. You can create a poll on Facebook from a Facebook page...

AMAZON DEALS

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

ADVERTISEMENTS

BEST OF ITECHGUIDES

How to Turn Off User Account Control (UAC) in Windows 10 (3 Methods)

How to Turn Off User Account Control (UAC) in Windows 10 (3 Methods)

Introduction This guide demos how to turn off UAC in Windows 10. User Account Control (UAC) is a security...
How to Set Out of Office Auto Reply in Outlook, Outlook.com and Gmail

How to Set Out of Office Auto Reply in Outlook, Outlook.com and Gmail

Introduction This guide demos how to set out of office auto reply in Outlook, Outlook.com and Gmail.
Powershell Function

PowerShell Function: Syntax, Parameters, Examples

Introduction There are two types of PowerShell Functions, basic and advanced. A basic PowerShell Function is a list of...
Best Family Movies on Netflix

The 10 Best Family Movies on Netflix

When you watch an awesome family movie on Netflix, it gives a lasting experience for the family. Most importantly, it creates beautiful...

How to Fix DISM Error 87 in Windows Server 2016 and Windows 10

Introduction DISM Error 87 in Windows Server 2016 can be cause by any of the following:

RECENT POSTS

How to Change Facebook Page Name

How to Change Facebook Page Name

This Itechguide teaches you how to change Facebook page name. The guide covers steps to change Facebook page name from a PC...
How to Change YouTube Channel Name

How to Change YouTube Channel Name

This Itechguide teaches you how to change YouTube channel name. The guide covers steps to change YouTube channel name from a PC.
How to Bold Text on Facebook Post

How to Bold Text on Facebook Post

This Itechguide teaches you how to bold text on Facebook post. The guide covers steps to bold text on Facebook post from...
Best Crime Documentaries on Netflix

The 10 Best Crime Documentaries on Netflix

This Itechguide reviews the 10 best crime documentaries on Netflix. The documentaries are listed in reverse order – with the 10th documentary...
Best Netflix Series

The 10 Best Netflix Series

This Itechguide reviews the 10 best Netflix series. The series are listed in reverse order – with the 10th series on top....

AMAZON DEALS

MUST READ

how to move columns in excel

How to Move Columns to Rows and Rows to Columns in Excel

Introduction If you receive some Excel data in columns, you can easily move the columns to rows in Excel...
Walmart Baby Registry

Walmart Baby Registry: Your Definitive Guide

Introduction Walmart Baby Registry gives expectant mothers a place to create a list of items they need for their...
Windows Server 2016 Interview Questions and Answers

Windows Server 2016 Interview Questions and Answers

Introduction This guide provides common Windows Server 2016 interview questions and answers. The questions are...
enable clean boot windows 10

How to Enable Clean Boot in Windows 10

Introduction This guide demos steps to Enable "Clean Boot" in Windows 10. "Clean Boot" is a Windows troubleshooting tool...
Hotmail email (outlook.live.com)

Hotmail Email (Outlook.com Email): Your Definitive Guide

Introduction I suppose the first question on your mind is, "what happened to Hotmail email?" This is because when...

AMAZON DEALS

By using this website you agree to accept our Privacy Policy and Terms & Conditions