SUBSTITUTE Function

Description

SUBSTITUTE function substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])

Parameters

Text Required. The text or the reference to a cell containing text for which you want to substitute characters.
Old_text Required. The text you want to replace.
New_text Required. The text you want to replace old_text with.
Instance_num Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Examples

Example 1

The example may be easier to understand if you copy A1:C10 to a blank worksheet.

1
2
3
4
5
6
7
8
9
10
A B C
Data
Excel Bible
Quarter 1, 2014
Quarter 1, 2011
Excel
Formula Result Description
=SUBSTITUTE(A2,"Excel","Word") Word Bible Substitutes Word for Excel.
=SUBSTITUTE(A3, "1", "2", 1) Quarter 2, 2014 Substitutes first instance of "1" with "2".
=SUBSTITUTE(A4, "1", "2", 3) Quarter 1, 2012 Substitutes third instance of "1" with "2".
=SUBSTITUTE(A5, "e", "X") ExcXl Substitutes X for e.

Example 2: count specific characters in a cell

The example may be easier to understand if you copy A1:C7 to a blank worksheet.

1
2
3
4
5
6
7
A B C
Data
Google BOOKS
Formula Result Description
=LEN(A2)-LEN(SUBSTITUTE(A2,"o","")) 2 Count how many times "o" appears in a cell.
=LEN(A2)-LEN(SUBSTITUTE(A2,"O","")) 2 Count how many times "O" appears in a cell.
=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"O","")) 4 Use UPPER to count lower and upper case.
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"o","")) 4 Use LOWER to count lower and upper case.

If you need to count how many times a specific character appears in a cell, you can do so with a formula that uses SUBSTITUTE and LEN.

SUBSTITUTE is a case sensitive function, so it will match case when running a substitution. If you need to count both upper and lower case occurrences of a specific character, use the UPPER (or LOWER) function inside SUBSTITUTE to convert the text to uppercase (or lowercase) before running the substitution. Then supply an uppercase (or lowercase) character as the text that's being substituted.

Video training

See also: Excel text functions

ASC
Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXT
Converts a number to text, using the ß (baht) currency format
CHAR
Returns the character specified by the code number
CLEAN
Removes all nonprintable characters from text
CODE
Returns a numeric code for the first character in a text string
CONCATENATE
Joins several text items into one text item
DOLLAR
Converts a number to text, using the $ (dollar) currency format
EXACT
Checks to see if two text values are identical
FIND, FINDB
Finds one text value within another (case-sensitive)
FIXED
Formats a number as text with a fixed number of decimals
WIDECHAR
Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT, LEFTB
Returns the leftmost characters from a text value
LEN, LENB
Returns the number of characters in a text string
LOWER
Converts text to lowercase
MID, MIDB
Returns a specific number of characters from a text string starting at the position you specify
PHONETIC
Extracts the phonetic (furigana) characters from a text string
PROPER
Capitalizes the first letter in each word of a text value
REPLACE, REPLACEB
Replaces characters within text
REPT
Repeats text a given number of times
RIGHT, RIGHTB
Returns the rightmost characters from a text value
SEARCH, SEARCHB
Finds one text value within another (not case-sensitive)
SUBSTITUTE
Substitutes new text for old text in a text string
T
Converts its arguments to text
TEXT
Formats a number and converts it to text
TRIM
Removes spaces from text
UPPER
Converts text to uppercase
VALUE
Converts a text argument to a number

Leave a Reply

Your email address will not be published. Required fields are marked *