# How to Use Substring Functions in Excel (Types and Examples)

Get FREE Advanced Excel Exercises with Solutions!

Usually, we need to work with substring functions in Excel. This is a guide that will let you how to separate, split, and remove substrings and numbers from text using Excel Functions and VBA. However, in this article, we will show you how to use Excel Substring functions with some relevant examples.

## 12 Different Types and Examples of Substring Functions in Excel

We have demonstrated 12 different types of substring functions altogether that we use regularly in Excel. Additionally, we will try to provide a clear idea about the functions. However, the substring functions are UPPER, LOWER, PROPER, CHAR, REPLACE, TRIM, LEN, LEFT, RIGHT, MID, FIND, and SEARCH.

### 1. UPPER, LOWER, and PROPER Functions: Syntax and Arguments

Basically, with the UPPER, LOWER or PROPER functions, you can change the case of text to uppercase, lowercase, or proper case easily. Usually, these are quite common substring functions in Excel. For example, the UPPER function can change a text to all uppercase whereas The LOWER function can change a text to all lowercase, and the PROPER function will capitalize the first letter of each word. In addition to this, these functions are very simple to use.

Syntax:

The syntax is the same for all three functions. For example, the syntax of the UPPER function is shown below.

`=UPPER(text)`

`=LOWER(text)`
`=PROPER(text)`

Arguments:

The syntax has only one argument. Moreover, it is a required argument or parameter.

Argument Required/Optional Explanation
text Required  It is a string of text where all letters have to be converted to upper, lower, or proper cases.

#### Examples of UPPER, LOWER and PROPER Functions

These functions are very easy to use. For example, we have taken some names to operate these functions.

Steps:

• Firstly, click on a cell.
• Then, write the formula mentioned below to get all letters in upper case.

`=UPPER(B5)` • However, you can also enter the below formula to get all letters in lowercase.

`=LOWER(B9)` • Lastly, in order to get the proper case, insert the following formula.

`=PROPER(B13)` • Finally, you will get all the data according to your desired case, as in the following image. However, you can use the AutoFill tool to apply the same formula to the cells below in a column. ### 2. CHAR Substring Function: Syntax and Arguments

In General, the CHAR function can convert a number (from 1 to 255) into ASCII characters. For example, CHAR(65) yields A, and CHAR(66) returns B. Meanwhile, you can see that the CHAR function is useful when you want to type characters that are awkward or impossible to type directly.

Syntax:

`=CHAR(number)`

Arguments:

The syntax has only one argument. However, it is a required argument or parameter.

Argument Required/Optional Explanation
number Required A number between 1 to 255 assigned to a specific character.

#### Example of CHAR Function

For example, I will show you the code number and corresponding character by using the CHAR function. Hence, follow the steps below.

Steps:

• Firstly, write the code number.
• Secondly, select a cell and write the following formula.

`=CHAR(B5)` • Lastly, press Enter and you will get your desired character. ### 3. REPLACE Function: Syntax and Arguments

Meanwhile, the REPLACE function is used to replace a part of a text string with a different text string. Moreover, the function returns with the new text string within which new and replaced text or word is present.

Syntax:

`=REPLACE(old_text, start_num, num_chars, new_text)`

Arguments:

Basically, the syntax has four arguments. In addition to this, all of them are required arguments or parameters. Hence, the parameters are mentioned below.

Argument Required/Optional Explanation
old_text Required The text within which a part has to be replaced.
start_num Required The starting number of the character of the part that has to be replaced.
num_chars Required The number of characters that have to be replaced with a new text.
new_text Required The text that has to be added by replacing the old one in the text string.

#### Example of REPLACE Function

For example, you want to replace the initial part of a product ID. Eventually, you can follow the steps mentioned below to do it easily.

Steps:

• Firstly, select a cell.
• Then, write down the following formula.

`=REPLACE(B5,1,2,”RS”)` • After that, press the Enter button and the previous ID will be replaced by the new ID. Read More: Excel VBA: How to Replace Text in String

### 4. TRIM Substring Function: Syntax and Arguments

Usually, The TRIM function can be used to remove all spaces from a text string except for single spaces between words. Hence, it is well known that the TRIM function can remove leading and trailing spaces. Moreover, it can also replace multiple consecutive spaces with a single space.

Syntax:

`=TRIM(text)`

Arguments:

The syntax has only one argument. In this case, it is a required argument or parameter.

Argument Required/Optional Explanation
text Required It is the main string of text.

#### Example of TRIM Function

For the purpose of demonstration, we have taken some movie names with additional spaces. Accordingly, we will show you how the TRIM function works with some easy steps.

Steps:

• Firstly, select the cell where you want to apply the function.
• Next, you have to write a formula as below.

`=TRIM(B5)` • Finally, you will find the names without any spaces. ### 5. LEN Function: Syntax and Arguments

In General, the LEN function can be used to return the number of characters in a text string. This function is both a simple and widely used substring function in Excel. Moreover, this function works with numbers, but number formatting is not included. Hence, the LEN function returns zero in terms of empty cells.

Syntax:

`=LEN(text)`

Arguments:

The syntax has only one argument. However, it is a required argument or parameter.

Argument Required/Optional Explanation
text Required It is the main string of text for which to calculate length.

#### Example of LEN Function

For example, we have used the same data to find the use of the LEN function. You have to follow some steps similarly.

Steps:

• At first, click on the cell named ‘Name After TRIM’.
• Then, write the formula mentioned below in another cell.

`=LEN(B5)` • At last, it will show you the number of characters as in the image shown below if you press Enter. ### 6. LEFT, RIGHT, and MID Functions: Syntax and Arguments

Functions such as LEFT, RIGHT, and MID can help you extract a word or text from another text string. In addition to this, the three substring functions in Excel will provide three different parts of a particular string.

#### 6.1 LEFT Function

The LEFT function is categorized under the TEXT function in Excel. Generally, this function returns a specified number of characters from the start of the provided text string. Above all, The LEFT function returns the first num_chars characters in the text string.

Syntax:

`=LEFT(text, [num_chars])`

Arguments:

The syntax has also two arguments. Both of them are required arguments or parameters. The parameters are mentioned below.

Argument Required/Optional Explanation
text Required text is the main string where you want to apply the function.
num_chars Required num_chars is the number of characters that will be extracted during the operation.

Example of LEFT Function

The LEFT function is quite easy to use in Excel. Additionally, I will show you some steps to use the function.

Steps:

• Firstly, click on a cell except for the data cell.
• Secondly, write a similar formula as below.

`=LEFT(B5,10)` • Lastly, press Enter from the keyboard. you will get the left part of the sample data in the below image.

###  #### 6.2 RIGHT Function

Generally, the RIGHT function returns the last num_chars characters in the text string. Moreover, the RIGHT function will extract digits from numbers as well as text. But this function does not consider the formatting of any cell. Like a date, currency, etc.

Syntax:

`=RIGHT(text, [num_chars])`

Arguments:

The syntax has two arguments. However, both of them are required arguments or parameters. Hence, the parameters are mentioned below.

Argument Required/Optional   Explanation
text Required text is the main string where you want to apply the function.
num_chars Required num_chars is the number of characters that will be extracted during the operation.

Example of RIGHT Function

Similarly, you can apply the RIGHT function to extract the data from the extreme right. Certainly, the process is the same as before.

Steps:

• Initially, select a cell and enter the formula below.

`=RIGHT(B5,13)` • Finally, you will get your desired part of the data. Read More: How to Split String by Length in Excel

#### 6.3 MID Function

The MID function returns a string containing a specified number of characters from the start position of a string. Usually, it is used to separate specific characters within a string.

Syntax:

`=MID(text,start_num,num_chars)`

Arguments:

The syntax has three arguments. However, all of them are required arguments or parameters. Hence, the parameters are mentioned below.

Argument Required/Optional Explanation
text Required The string from which characters will be extracted. It can be any text value, number, or array.
start_num Required The starting position from which characters will be extracted. It can be a single number or an array of numbers.
num_chars Required The total number of characters that will be extracted. It can be a single number or an array of numbers.

Example of MID Function

Usually, the Mid function provides data from the middle part of a string. Similarly, follow the steps to use the function.

Steps:

• At first, click on a cell.
• Next, type the formula into the cell.

`=MID(B5,11,15)` • After that, press Enter in order to receive the middle part of the data. ### 7. FIND/SEARCH Functions: Syntax and Arguments

However, in some situations, you need to find data automatically from other cells. For this purpose, the FIND and SEARCH substring functions in Excel are both very common.

#### 7.1 FIND Function

In Microsoft Excel, the FIND function is generally used to extract the position of a defined text in a cell containing a text string. Moreover, the FIND function returns the starting position of a case-sensitive text string within another text string.

Syntax:

`=FIND(find_text, within_text, [start_num])`

Arguments:

The syntax has also three arguments. Moreover, two of them are required arguments or parameters, and one of them is an optional argument. The parameters are mentioned below.

Argument Required/Optional Explanation
find_text Required A text or a part of a text to be searched for in a cell containing another text string.
within_text Required The cell containing the text is where the defined character or part of the text will be searched for.
[start_num] Optional Defined position in the text string from where the character count will be initiated.

Example of FIND Function

For example, I will show you some steps gradually so that you can complete the operation easily and save time.

Steps:

• Firstly, click on the cell named ‘Position’.
• Next, enter the following formula into the cell.

`=FIND(C5,B5)` • Lastly, it will return the position of the desired item from the sample data. #### 7.2 SEARCH Function

In Microsoft Excel, the SEARCH function returns generally the number of characters at which a specific character or text string is first found, reading from left to right. Moreover, it is available in Excel 2003. Usually, it works for both array and non-array Formulas. In addition to this, the SEARCH function is not case-sensitive.

Syntax:

`=SEARCH(find_text, within_text, [start_num])`

Arguments:

The syntax has three arguments. In addition to this, two of them are required arguments or parameters and one of them is an optional argument. Hence, the parameters are mentioned below.

Argument Required/Optional Explanation
find_text Required The text that is searched for. Can be a single text or an array of texts.
within_text Required The text value within which the find_text argument is searched for. Can be a single text value or an array of text values.
[start_num] Optional The position of the within_text argument from which it starts searching. It can be a single number or an array of numbers. Default is 1.

Example of SEARCH Function

For example, we have used the same data to find the use of the SEARCH function. Similarly, you have to follow the steps below.

Steps:

• Firstly, click on a cell and write the following formula in order to search for a particular item.

`=SEARCH(“Novel”,C11,1)` • Secondly, if you press Enter, it will return the position of the search keyword. ## 4 Examples to Extract Substring Before/After Specific Text or Character

For the purpose of demonstration, we have combined multiple substring functions into a single dataset in Excel. Hence, we have divided it into three parts. Go through it carefully.

### Example 1: Combining UPPER, LEFT, and SEARCH Substring Functions

In this part, we have combined UPPER, LEFT, and SEARCH functions. You need to follow the steps below.

Steps:

• At first, select a cell other than the data set.
• Now, enter the below formula on the formula bar.

`=UPPER(LEFT(B5,SEARCH("-",B5,1)-1))` Formula Breakdown

• =UPPER(LEFT(B5,SEARCH(“-“,B5,1)-1))
• Generally, the UPPER function changes all lowercase letters to uppercase.
• The LEFT function provides the left part of the string.
• The SEARCH function will return the number of characters at which a specific character or text string is first found, reading from left to right. It will count characters up to “”.
• For example, we have selected column B5.
• Hence, the formula will extract data and provide the output accordingly.
• After that, you will get the left side of the data with all uppercase letters if you press Enter on the keyboard.
• Finally, use the AutoFill tool to get the same result for the below cells in the column. ### Example 2: Merging PROPER, MID, and SEARCH Functions

For example, we will use PROPER, MID, and SEARCH functions combined. For this reason, you have to follow the steps below.

Steps:

• Firstly, click on a cell.
• Secondly, you have to write the following formula.

`=PROPER(MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1))` Formula Breakdown

• =PROPER(MID(B5, SEARCH(“-“,B5) + 1, SEARCH(“-“,B5,SEARCH(“-“,B5)+1) – SEARCH(“-“,B5) – 1))
• Normally, the PROPER function changes all letters to the proper case.
• The MID function provides the data from the middle part of the string.
• The SEARCH function will return the number of characters at which a specific character or text string is first found, reading from left to right. It will count characters up to “”.
• For example, we have selected column B5.
• Hence, the formula will extract the middle part of the data and provide the output accordingly.
• Thirdly, press the Enter key to get your final result. ### Example 3: Nesting LOWER, RIGHT, LEN, and SEARCH Substring Functions

In this part, we are going to demonstrate the LOWER, RIGHT, LEN, and SEARCH functions. Hence, follow the steps below in order to complete the operation smoothly.

Steps:

• At first, click on a particular cell on the sheet.
• Then, you have to write a similar formula as mentioned below.

`=LOWER(RIGHT(B5,LEN(B5)-SEARCH("-",B5, SEARCH("-",B5) + 1)))` Formula Breakdown

• =LOWER(RIGHT(B5,LEN(B5)-SEARCH(“-“,B5, SEARCH(“-“,B5) + 1)))
• In general, the LOWER function changes all letters to the lowercase in a string.
• The RIGHT function provides the data from the extreme right part of the string.
• The LEN function returns the number of characters in a text string.
• In addition to this, The SEARCH function will return the number of characters at which a specific character or text string is first found, reading from left to right.
• Meanwhile, it will count characters up to “”. For example, we have selected column B5.
• Similarly, the formula will extract the right part of the data and provide the output in lowercase.
• After that, press the Enter key. ### Example 4: Applying VBA to Extract Substring

Basically, letters and digits are placed together in a way that you cannot find a rule to split them easily. In this case, we can use the VBA code to complete this task. Moreover, we can easily apply it to multiple substring functions in Excel. For example, let’s take the following figure. Steps:

• Firstly, open the worksheet where you want the text to be split.
• Now, hold Alt+F11 keys in Excel which opens the Microsoft Visual Basic Applications window.
• Then, click the Insert button.
• After that, click on Module from the menu to create a module. • A new window will open. Hence, write the following VBA macro in the Module window.
``````Sub extract_click()
Dim StrA As String
Dim StrB As String
Dim StrC As String
Dim str As String
ThisWorkbook.Worksheets("VBA").Activate
nrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
'Exceldemy Publications
For i = 5 To nrow
For j = 3 To 5
ActiveSheet.Cells(i, j) = ""
Next j
Next i
For i = 5 To nrow
str = ActiveSheet.Cells(i, 2)
For k = 1 To Len(str)
temp = Mid(str, k, 1)
If temp Like "[A-Za-z]" Then
StrA = StrA & temp
ElseIf temp Like "[0-9]" Then
StrB = StrB & temp
Else
StrC = StrC & temp
End If
Next k
ActiveSheet.Cells(i, 3) = StrA
ActiveSheet.Cells(i, 4) = StrB
ActiveSheet.Cells(i, 5) = StrC
StrA = ""
StrB = ""
StrC = ""
Next i
End```` Sub```` VBA Code Breakdown

• Firstly, we create a new procedure Sub in the worksheet using the below statement.
``````Sub extract_click()
``````
• Secondly, we declare variables as
``````Dim StrA As String
Dim StrB As String
Dim StrC As String
Dim str As String``````
• Thirdly, we activate the VBA sheet and select the range for nrow.
``````ThisWorkbook.Worksheets("VBA").Activate
nrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row``````
• Now, we applied the For loop to i and j.
``````For i = 5 To nrow
For j = 3 To 5
ActiveSheet.Cells(i, j) = ""
Next j
Next i``````
• Again, we applied two For loops and declared 4 strings- str, StrA, StrB and StrC.
``````For i = 5 To nrow
str = ActiveSheet.Cells(i, 2)
For k = 1 To Len(str)
temp = Mid(str, k, 1)
If temp Like "[A-Za-z]" Then
StrA = StrA & temp
ElseIf temp Like "[0-9]" Then
StrB = StrB & temp
Else
StrC = StrC & temp``````
• After that, we started an END If function and activated the cells according to the strings.
``````End If
Next k
ActiveSheet.Cells(i, 3) = StrA
ActiveSheet.Cells(i, 4) = StrB
ActiveSheet.Cells(i, 5) = StrC
StrA = ""
StrB = ""
StrC = ""
Next i``````
• Finally, we end the Sub of the VBA macro as
``End Sub``
• At last, press the F5 key to run the VBA code.
• Finally, you will get the split data on your desired columns. ## Conclusion

These are all the steps you can follow in Excel to use the Substring Function in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

1. Reply Roger Gruenenfelder Dec 18, 2016 at 7:49 AM

It leads to “Extract Data from a Webpage to Excel” instead to the post above.
Regards Roger

• Reply Roger, thanks a lot. I did not notify the issue. It’s a crucial problem. I am working on it. Advanced Excel Exercises with Solutions PDF  