Return substring
This tutorial shows how to extract a substring with a nominated start and end position using an Excel formula or VBA
Example: Return substring
|
=MID(B5,C5,D5-C5+1)
|
This formula uses the Excel MID function with the number of characters to return being calculated between the start and end position, plus one, to return a substring.
|
Sub Return_substring()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'get the substring
ws.Range("E5") = Mid(ws.Range("B5"), ws.Range("C5"), ws.Range("D5") - ws.Range("C5") + 1)
End Sub
EXPLANATION
This tutorial shows how to extract a substring through the use of an Excel formula or VBA.
The Excel and VBA methods both use the MID function with the number of characters to return being calculated between the start and end position, plus one, to return a substring.
FORMULA
=MID(text, start_num, end_num-start_num+1)
=MID(text, start_num, end_num-start_num+1)
ARGUMENTS
text: The string from which to extract the characters.
start_num: The position of the first character in the string that is to be extracted.
end_num: The position of the last character in the string that is to be extracted.
text: The string from which to extract the characters.
start_num: The position of the first character in the string that is to be extracted.
end_num: The position of the last character in the string that is to be extracted.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return first word from a string | How to return the first word from a string using Excel and VBA | |
| Return last word from a string | How to return the last word from a string using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| MID Function | The Excel MID function returns the specified number of characters from a selected string, starting at a specified position |