Summary: The ampersand (&) is the concatenation character used to join text cells with other text cells or with literal values in a formula.įunctions Discussed: =PROPER() More Excel Tips
Immediately hit Ctrl+Z to undo the delete. This tells you that you have a formula that points to cells(s) that are no longer there. Gotcha: If you delete columns A and B while column C still contains formulas, then all of the formulas will change to #REF! errors, as shown in Fig. For more on the PROPER function and related tools, see Excel Text Function Variations. After using the PROPER function, you will have to manually fix any names with an interior capital letter. This will work for all of your names except names with an interior capital such as “Paul Mc- Cartney” or “Dave VanHorn”.
=PROPER(A2&“ ”&B2) will convert the names to Proper Case, as shown in Fig. 259.Īdditional Information: To convert BRITNEY SPEARS to Britney Spears, use the PROPER function. Copy this formula down to all of the cells in your range, as shown in Fig. If you join cell A2, a space in quotes, and cell B2, the answer will look acceptable.
Change the formulas in column C to values before deleting columns A and B. Strategy: Use the ampersand (&) as a concatenation operator in a formula in column C. However, if the the columns are text fields, as in this example, there is an easier way to combine them using the ampersand (&) operator. One way to do this is with Excel’s CONCATENATE function. You want to combine these into one column. 257 (click any image for a larger view), you have data with First Name in column A and Last Name in column B.