Return nth character

This tutorial shows how to extract the nth character from a string through the use of an Excel formula, with the MID function or VBA

EXCEL FORMULA 1. Return nth character using the MID function

EXCEL

Hard coded formula
Return nth character
Cell reference formula
Return nth character
=MID(B5,4,1)
=MID(B5,C5,1)
GENERIC FORMULA

=MID(string,nth_char,1)

ARGUMENTS
string: The string from which you want to return an nth character.
nth_char: A number which represents the nth character that you want to extract from a string.

GENERIC FORMULA

=MID(string,nth_char,1)

ARGUMENTS
string: The string from which you want to return an nth character.
nth_char: A number which represents the nth character that you want to extract from a string.

EXPLANATION

This formula uses the MID function to extract the nth character from a specific string.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the number that represents the nth character that you want to return from a string entered directly in the formula or referenced to a cell.

In this example we are extracting the third (3rd) character, from the left, from a string captured in cell B5.

VBA CODE 1. Return nth character using VBA

VBA

Hard coded against single cell
Sub Return_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'extract the 3rd character from a string
ws.Range("C5") = Mid(ws.Range("B5"), 3, 1)

End Sub

Cell reference against single cell
Sub Return_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'extract the 3rd character from a string
ws.Range("D5") = Mid(ws.Range("B5"), ws.Range("C5"), 1)

End Sub

Hard coded against range of cells
Sub Return_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'extract the 3rd character from a string

For x = 5 To 8

ws.Range("C" & x) = Mid(ws.Range("B" & x), 3, 1)
Next x

End Sub

Cell reference against range of cells
Sub Return_nth_character()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'extract the 3rd character from a string

For x = 5 To 8

ws.Range("D" & x) = Mid(ws.Range("B" & x), ws.Range("C" & x), 1)
Next x

End Sub

RELATED TOPICS

Related Topic Description Related Topic and Description
Insert a value after first nth character How to insert a value after the first nth character through the use of an Excel formula or VBA
Insert a value before first nth character How to insert a value before the first nth character through the use of an Excel formula or VBA
Insert a value after last nth character How to insert a value after the last nth character through the use of an Excel formula or 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