Magic Tricks

See how to Extract First and Last Names and rearrange them using a formula. For example, go from Smitty, Sioux Z. to Sioux Z. Smitty and from Radcoolinator, Sioux to Sioux Radcoolinator.
REPLACE function replaces part of a text string with text you specify, given a starting number and the number of characters
FIND is similar to SEARCH, but it is case sensitive. FIND finds the starting position in a text string of text you specify. Example Find "d" in "suds' returns 3.
LEFT returns a specified number of characters from text string from the left
TRIM removes spaces from a text string except for single spaces between words
The LEN function counts characters
MID extracts characters from the middle of a text string given a starting position and number of characters

Tricks From Mr Excel Message Board Aladin Ron