Extract text string between two characters

To extract a text string between two characters you can use a combination of Excel MID, SEARCH, FIND and LEN functions

Example: Extract text string between two characters

METHOD 1. Extract text string between two characters (case insensitive)

EXCEL

=MID(B9,SEARCH(C5,B9)+LEN(C5),SEARCH(C6,B9)-SEARCH(C5,B9)-LEN(C5))

The formula extracts the string between words 'provide' and 'and'. The Excel SEARCH function allows the formula to be case insensitive.
If there are any leading and trailing spaces, the formula will also extract them. To remove the leading and trailing apply the Excel TRIM function to the entire formula.

METHOD 2. Extract text string between two characters (case sensitive)

EXCEL

=MID(B9,FIND(C5,B9)+LEN(C5),FIND(C6,B9)-FIND(C5,B9)-LEN(C5))

The formula extracts the string between words 'provide' and 'and'. The Excel FIND function allows the formula to be case sensitive.
If there are any leading and trailing spaces, the formula will also extract them. To remove the leading and trailing apply the Excel TRIM function to the entire formula.

METHOD 1. Extract text string between two characters using VBA

VBA

Sub Extract_text_string_between_two_characters()

'declare a variable
Dim ws As Worksheet
Dim strtxt As String
Dim Fromtxt As String
Dim Totxt As String
Dim FromPos As Integer
Dim ToPos As Integer
Dim ExtractStr As Integer

Set ws = Worksheets("Analysis")
strtxt = ws.Range("B9")
Fromtxt = ws.Range("C5")
Totxt = ws.Range("C6")
FromPos = InStr(strtxt, Fromtxt)
ToPos = InStr(strtxt, Totxt)

'extract the text string between the two words
ExtractStr = Mid(strtxt, FromPos + Len(Fromtxt), ToPos - FromPos - Len(Fromtxt))

ws.Range("F8") = ExtractStr

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
String Range: In this example we are extracting a text string from cell ("B9").
From and To text: In this example cell ("C5") and ("C6") capture the values between which to extract a text string.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Worksheet Name: Select the worksheet in which this task is performed by changing the worksheet name (Analysis) in the VBA code.
String Range: Select the range that captures the string from which you are extracting a text string from by changing the cell reference ("B9") in the VBA code to any cell reference in the worksheet, that doesn't conflict with the formula.
From and To text: Select the from and to value by either changing the values in cells ("C5") and ("C6") or changing the from and to cell references in the VBA code.

Explanation about the formulas used to extract text string between two characters

EXPLANATION

EXPLANATION
To extract a text string between two characters you can use a combination of Excel MID, SEARCH, FIND and LEN functions. This tutorial provides both Excel and VBA methods.

FORMULAS (case insensitive)
=MID(str,SEARCH(from_val,str)+LEN(from_val),SEARCH(to_val,str)-SEARCH(from_val,str)-LEN(from_val))

FORMULAS (case sensitive)
=MID(str,FIND(from_val,str)+LEN(from_val),FIND(to_val,str)-FIND(from_val,str)-LEN(from_val))

ARGUMENTS
str: The string from which to extract a text string.
from_val: The value from which to begin extracting a text string.
to_val: The value up to which to extract a text string.