Zhiping Yan
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.
Download Practice Workbook
However, you can download the workbook used for the demonstration from the download link below.
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.
Read More: Excel Formula to Replace Text with Number (5 Examples)
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 (5 Effective Ways)
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.
Read More: How to Replace Text between Two Characters in Excel (3 Easy Ways)
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.
Read More: How to Split Text by Number of Characters in Excel (7 Ways)
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 functions 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.
Read More: How to Switch First and Last Name in Excel with Comma
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 (8 Ways)
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.
Read More: Excel VBA: Replace Character in String by Position (4 Effective Ways)
Similar Readings
- How to Extract Text Between Two Characters in Excel (4 Methods)
- Excel VBA: Open Word Document and Replace Text (6 Examples)
- How to Split Text in Excel by Character (5 Quick Methods)
- Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
- How to Extract Text between Two Spaces in Excel (5 Methods)
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.
Read More: Excel VBA: How to Find and Replace Text in Word Document
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.
Read More: How to Replace Text after Specific Character in Excel (3 Methods)
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.
Read More: How to Replace Text in Selected Cells in Excel (4 Simple Methods)
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.
Read More: How to Extract Text after Second Comma in Excel (6 Methods)
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.
- Finally, you will receive your desired output result as below.
Read More: How to Extract Text after a Specific Text in Excel (10 Ways)
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 EndSub
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.
Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
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.
For more information like this, visit ExcelDemy.com.
Related Articles
- How to Extract Certain Text from a Cell in Excel VBA (5 Examples)
- Excel VBA to Replace Blank Cells with Text (3 Examples)
- How to Replace Text with Blank Cell in Excel (5 Simple Methods)
- Excel VBA to Find and Replace Text in a Column (2 Examples)
- Split Text in Excel into Multiple Rows (6 Quick Tricks)
- How to Separate Text in Excel (10 Easy Methods)
Please check the .pdf link.
It leads to “Extract Data from a Webpage to Excel” instead to the post above.
Regards Roger
Roger, thanks a lot. I did not notify the issue. It’s a crucial problem. I am working on it.