Updated: Sep 28, 2021
Hi friend! I hope this article finds you well. In my last YouTube video, I used the ampersand (&) to join (or concatenate for fancy folk) text and combine the salesperson‘s name and the word: “Sales”. For example: “Jessica Sales”. Lets talk about how this little-known feature is useful.
Have you ever created a spreadsheet with contact information for clients or event attendees but the formats of the entries in a particular column are all over the place? For example, you expect a date to be entered in a one format, but people filling out the form enter dates in different formats? Or you ask people to enter their phone numbers and you expect the +256772123456 format and you get the 0772123456 format instead? The first solution is to use data validation to restrict the format of entries to prevent these problems from happening in the first place. The second solution is to change formats in the survey tool using validation criteria and regular expressions if the data comes from a survey tool. However, today‘s scenario is for the unfortunate person who stumbles upon an already messed up spreadsheet and must now troubleshoot.
My cousin, Julia reached out to me with a specific challenge: she had a list of customer information with the phone numbers in an undesirable format. Preview the data below:
Preview the data
Julia wants the phone numbers to begin with Uganda’s country code: +256. For example: John Doe’s phone number should be +256772123456.
To add “+256” to the beginning of the phone numbers, we add a column to the right of the Phone number column and give it a meaningful title.
In Cell C2, next to John Doe’s number, we type an equal sign, +256 surrounded by double quotation marks, add the ampersand (&), and click inside cell B2 to get the rest of the phone number.
Once we click enter, the phone number is now in the desired format:
Once we are sure the phone number is in the desired format, we copy the formula downwards.
We solved Julia’s problem in record time!
Julia’s example is just one case. You can use the ampersand to join any entries in cells in a meaningful way.
What if Julia wanted to create passwords for the clients using a combination of their names and their phone numbers? Julia starts with = then types “John”, &, clicks inside cell B2 to get the phone number, types & again, then types “Doe”. The ampersands join (or concatenate) “John”, “772123456” and “Doe” together.
The final password is ready:
What if you want to use text from different cells to create a sentence? We use ampersands to connect all the components of the sentence, including the spaces and commas. Remember, all entries to be combined, except cell references, must be surrounded by double quotation marks (“”):
Pay attention to the spaces & punctuation marks
After pressing ENTER, our final result is a complete punctuated sentence. While you may never to use the ampersand to create sentences (because you know how to type), this example shows you how versatile the ampersand can be.
And there you have it. The ampersand is useful in data cleaning for joining text(s), concatenation, and so on and so forth.
If you have any specific challenges you’d like to solve, please contact me. I would love to hear from you. Otherwise, talk to you next week. Be blessed!
©[year] Rights Reserved
