Reversing Names - Excel Text


« Back to videos

Additional Information:

This video illustrates how you can construct Excel formulas to manipulate text strings. It refers to the LEFT, RIGHT, MID, LEN and FIND functions, which are covered individually elsewhere.

The point of the above functions is to break down a string of text e.g. "Joe Sixpack" into smaller strings such as "Joe" and "Sixpack". This raises the question of how you can put text back together in Excel. The answer is to use the ampersand symbol. So the formula ="Joe Sixpack" is identical to ="Joe " & "Sixpack"

Note that when using text, we have to surround text with quotation marks. This stops Excel from confusing our text with range names. If Joe was written in cell A1, and Sixpack in cell A2, we could re-write our formula =A1&" "&A2. The cell addresses are variables as their content could change. Therefore they do not need the quotation marks. Quotation marks are reserved for text strings that do not change. In this case, we introduce a space between first name and surname. The space is a character that will not change so we require quotation marks.