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
| LEN | Counts the number of characters | =LEN(“antidisestablishmentarianism”) returns 28 |
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
Ask Quincy 
Reblogged this on SutoCom Solutions.