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.

Advertisement

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)

Advertisement

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

Advertisement
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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

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

BEST OF ITECHGUIDES

Excel Count

Excel Count: How to Count in Excel With Examples

Introduction Excel COUNT Function is used for counting items in a worksheet. Excel COUNT also has the conditional function,...
Active Directory FSMO Roles

Active Directory FSMO Roles Explained

Introduction This article offers a simplified explanation of the 5 Active Directory FSMO (pronounced "FisMO") roles.
powershell add ad computers to array

How to Add AD Computers to a Powershell Array

Introduction There is one way to add AD Computers to a PowerShell array. A PowerShell array is a data...
0xc000021a: Status System Process Terminated Error [Fixed]

0xc000021a: Status System Process Terminated Error [Fixed]

Introduction You may receive "0xc000021a (STATUS_SYSTEM_PROCESS_TERMINATED)" error after installing a new device on your Windows computer. You may also...
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...

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Windows could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

Active Directory Domain Services

Active Directory Domain Services: Installation & Configuration

What is Active Directory Domain Services? Active Directory Domain Services (AD DS) is at the core of Microsoft's Directory...

Windows 7 Safe Mode: How to Start Windows 7 in Safe Mode

Introduction If need to perform some advanced troubleshooting on Windows 7, then you may need to start Windows 7...
WSUS

WSUS (Windows Server Update Service): Installation and Configuration

What is WSUS Server? WSUS (Windows Server Update Service) is a Microsoft Server role that allows download and installation...
disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
WSCRIPT and CSCRIPT Commands: Syntax, Parameters, Examples

WSCRIPT and CSCRIPT Commands: Syntax, Parameters, Examples

Introduction WSCRIPT is the Windows-based version of Windows Script Host while CSCRIPT is the command-prompt-based version. This guide...

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