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

**. However, in this article, we will show you how to use**

*VBA***with some relevant examples.**

*Excel Substring functions*## 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***, and**

*FIND***.**

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

Basically, with ** the UPPER**,

**or**

*LOWER***, 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,**

*PROPER functions***can change a text to all uppercase whereas**

*the UPPER function***can change a text to all lowercase, and**

*The LOWER function*

**the****will capitalize the first letter of each word. In addition to this, these functions are very simple to use.**

*PROPER function***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
tool to apply the same formula to the cells below in a column.*AutoFill*

### 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,

**yields**

*CHAR(65)***, and**

*A***returns**

*CHAR(66)***. Meanwhile, you can see that**

*B***is useful when you want to type characters that are awkward or impossible to type directly.**

*the CHAR function***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
and you will get your desired character.*Enter*

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

**can remove leading and trailing spaces. Moreover, it can also replace multiple consecutive spaces with a single space.**

*the TRIM function***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 *

**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,**

*LEN function***returns zero in terms of empty cells.**

*the LEN function***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**,

**, and**

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

*MID*#### 6.1 LEFT Function

** The LEFT function** is categorized under

*the*

*TEXT***in Excel. Generally, this function returns a specified number of characters from the start of the provided text string. Above all,**

*functions***returns the first num_chars characters in the text string.**

*The LEFT function***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
from the keyboard. you will get the left part of the sample data in the below image.*Enter*

**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,

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

*the RIGHT function***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
in order to receive the middle part of the data.*Enter*

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

**in Excel are both very common.**

*SEARCH substring functions*#### 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,

**returns the starting position of a case-sensitive text string within another text string.**

*the FIND function***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,

**is not case-sensitive.**

*the SEARCH function***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
, it will return the position of the search keyword.*Enter*

**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**,

**and**

*LEFT,***functions. You need to follow the steps below.**

*SEARCH***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**changes all lowercase letters to uppercase.*UPPER function* provides the left part of the string.*The LEFT 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 “*The SEARCH function*”.*–*- 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
on the keyboard.*Enter* - Finally, use the
tool to get the same result for the below cells in the column.*AutoFill*

**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**,

**and**

*MID,***combined. For this reason, you have to follow the steps below.**

*SEARCH functions***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,
changes all letters to the proper case.*the PROPER function* provides the data from the middle part of the string.*The MID 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 “*The SEARCH function*”.*–*- 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
key to get your final result.*Enter*

**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***and**

*LEN,***. Hence, follow the steps below in order to complete the operation smoothly.**

*SEARCH functions***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**changes all letters to the lowercase in a string.*LOWER function* provides the data from the extreme right part of the string.*The RIGHT function***The**returns the number of characters in a text string.*LEN function*- In addition to this,
will return the number of characters at which a specific character or text string is first found, reading from left to right.*The SEARCH function* - 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
key.*Enter* - 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**+*keys in Excel which opens the**F11**window.*Microsoft Visual Basic Applications* - Then, click the
button.*Insert* - After that, click on
from the menu to create a module.*Module*

- A new window will open. Hence, write the following
**VBA**macro in thewindow.*Module*

`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
in the worksheet using the below statement.**Sub**

```
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
sheet and select the range for*VBA*.*nrow*

```
ThisWorkbook.Worksheets("VBA").Activate
nrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
```

- Now
**,**we applied theto i and j.*For loop*

```
For i = 5 To nrow
For j = 3 To 5
ActiveSheet.Cells(i, j) = ""
Next j
Next i
```

- Again, we applied two
and declared 4 strings-*For loops*,*str*,*StrA*and*StrB*.*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
function and activated the cells according to the strings.*END If*

```
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
key to run the*F5*code.**VBA** - 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.