Overview
Creating VCards for your contacts can be useful to create scan-able QR codes or VCF files.
This has many use cases, such creating QR codes for event attendees which are universally scan-able by any QR code scanner.
The Formula
The following spreadsheet formula creates VCards that include first and last name, email, job title, company, phone and address.
="BEGIN:VCARD"&CHAR(10)&"VERSION:4.0"&CHAR(10)&"N:"&B2&";"&A2&CHAR(10)&"EMAIL:"&C2&CHAR(10)&"TITLE:"&D2&CHAR(10)&"ORG:"&E2&CHAR(10)&"TEL;TYPE=voice,work,pref:"&F2&CHAR(10)&"ADR;CHARSET=UTF-8;TYPE=HOME:"&";;"&G2&";"&H2&";"&I2&";"&J2&";"&K2&CHAR(10)&"END:VCARD"
Note: The formula assumes that the first row of the sheet is used for the headers (ex. "First Name", "Last Name", "Email"..etc) and that the second row has the contact information.
Also Note that the address is separated to 5 different cells since it has to be in the following format:;;Street;City;Region;Postcode;Country
Ex. ADR;CHARSET=UTF-8;TYPE=HOME:;;Wallstr. 1;Berlin;;12345;Germany
;;Wallstr. 2;Austin;Texas;12345;United States
To add more fields to the formula, simply add &CHAR(10)&"[fieldname]:"&[cell] and make sure that &CHAR(10)&"END:VCARD" is always at the end of the formula.
Adding More Fields to the Formula
A list of other VCARD fields with formatted examples can be found here:
Formula in Action
An example of the formula in action can be found here, including a 3rd party online QR code generator.
Pro Tip: Copying Formula Output
If you need to copy the VCard output from the sheet, make sure to paste them into another column 'as value'.
Pasting Values in Google Sheets
To paste the values in Google Sheets, use the 'Paste Special' feature and select 'Values Only'.
Pasting Values in Excel
To paste values on Excel, select the paste values option as shown:
