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.
Scenario
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:
Julia wants the phone numbers to begin with Uganda’s country code: +256. For example: John Doe’s phone number should be +256772123456.
Using the ampersand
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!
Bonus features: More about the ampersand
Julia's example is just one case. You can use the ampersand to join any entries in cells in a meaningful way.
Multiple ampersands
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:
Using ampersands and spaces
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 (""):
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.
Conclusion
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!
PS: The Excel file for this week's YouTube video where we create a Price Picker using Lookup references is here ↓
Comments