Excel Text 4 of 4: Using SUBSTITUTE to Parse Text

Excel is great at splitting text across multiple columns. For basic text separation (e.g. putting first and last name in separate columns) the LEFT and RIGHT functions work fine. But these functions fall short when applied to data that follow an inconsistent pattern. In the below example, the errant inclusion of a middle name wrecks havoc with our formula results.

Using the SUBSTITUTE function one can identify and replace the last space in the cell with a uniquely identifying character (e.g., an underscore). This character can then be used as a delimiter to separate Lastname from full name.

SUBSTITUTE: replace character(s) within a cell with specified character(s)

= SUBSTITUTE ( text, old text, new text, instance)
Note, instance is optional; when omitted, every occurrence of old text is replaced with the new.

Examples:

• =SUBSTITUTE(“Mad Max”, “a”,“i”) returns Mid Mix
• =SUBSTITUTE(“mississippi”, “i”,“”) returns msssspp
• =SUBSTITUTE(“banana”, “a”,“Q”,2) returns banQna

Click here for a 5 minute tutorial:

Cheers!

hɔuᴉnb

Additional reading:

• Excel Text 1 of 4: LEFT() and RIGHT()
• Excel Text 2 of 4: Changing Case with PROPER(), UPPER() & LOWER()
• Excel Text 3 of 4: Using SEARCH(), MID() & CELL() to Display Sheet Name
• More Excel Tips

Author: quincy harley jr

Quincy is a technophile, coach and Learning Development expert. He has an extensive legal IT background and is practiced in MS Office application support and product development. As a project leader he has been integral in numerous new application rollouts. Whatever spare time he has, is spent with reading, archery and watching his young sons grow. View all posts by quincy harley jr