Excel Tip #9: Quickly Create Similarly Formatted Data
If you work with large data sets, you may some day be tasked with combining the data for some other purpose.
For example, a webmaster may need to generate links to products on a website with certain tracking IDs.
In the example above, the spreadsheet has a column for Product Number and Tracking ID. The two pieces of data don’t generate a usable URL on their own, so you might need to combine them with some additional data to get something that might work.
Using the following formula will concatenate the domain name, Product Number after the domain name, add the ?trackID parameter, and finally add the tracking ID to the end.
="http://example.com/"&A2&"?trackID="&B2
Of course, you can use this for any sort of data that is consistently formatted. The formula format is simple:
- = – This begins the formula
- To start with text like the http://example.com/, simply enclose the text in double quotes.
- To add a cell reference after the text, put ampersands around the cell reference like this
&A2&. - Add another text reference by using double quotes again.
- Add another cell reference, but this time since there will be nothing following it like additional text, only use one ampersand like this:
&B2.
Try generating a list of email addresses where everyone’s email is FirstName.LastName@example.com, when you only have the First and Last names in your spreadsheet.