Text Manipulation

Q How can I get the first 5 characters from a cell?
A Use the "left" function. If the cell you want to extract the first 5 characters from is B5, the formula is =left(B5,5)

Q How can I get the last 5 characters from a cell?
A Use the "right" function. If the cell you want to extract the first 5 characters from is B5, the formula is =right(B5,5)

Q How can I get the middle 5 characters from a cell, starting at the third character?
A Use the "mid" function. If the cell you want to extract the first 5 characters from is B5, the formula is =mid(B5,3,5)

Q I have two cells, each containing part of an address. How can I combine these into one address?
A Use the "concatenate" function. If the cells you want to combine are A5 and B5, the formula is =A5&B5. (You can also use =concatenate(A5&B5) but why do the extra typing , right?)

Q How can I extract the first 5 characters after a dash "-" from a cell?
A Use the "find" function nested within the "mid" function. If the cell you want to extract 5 characters from is B5, the formula is =mid(B5,find("-",B5)+1,5). The "find" function will return the position number of the variable you are looking for, a dash in this example.

Q How can I convert a number that is formatted as text to a number that is formatted as numeric (sorting numbers formatted as text does not work)?
A Use the "value" function. If cell B2 contains the data you want to convert to a numeric format, the formula is =value(B2).

Q The data I have imported into my spreadsheet contains a bunch of trailing spaces, instead of "Davidson" the cell contains "Davidson_____" How do I get rid of the extra spaces?
A Use the "trim" function. If cell B2 contains the data you want to trim, the formula is =trim(B2). This function removes all spaces EXCEPT for single spaces between words.

Q How can I change the case in a cell from lower to all uppercase (all CAPITAL letters)?
A Use the "upper" function. If cell B2 contains the data you want to change the case, the formula is =upper(B2).

Q Just the opposite of the previous. How can I change the case in a cell from upper to all lowercase (all small letters)?
A Use the "lower" function. If cell B2 contains the data you want to change the case, the formula is =lower(B2).

Q One last question on case. How can I change the case in a cell from upper (or lower) to proper (first letter is capitalized and the rest are small letters)?
A Use the "proper" function. If cell B2 contains the data you want to change the case, the formula is =proper(B2). One caution on this, if you have a name like McDonald, then the "proper" function will return Mcdonald.