How to Open VCF Files in Excel (2 Methods)

Microsoft Excel is a powerful tool for working with datasets but opening VCF (Virtual Contact File) files directly in Excel can be a bit tricky. VCF files store business contact information and are commonly known as vCards. Although Excel lacks built-in support for .vcf formats, we can still open them using the following methods:

Method 1 – Text Import Wizard

Open the VCF File Using Excel:
- Click on File > Open.
- In the Open dialog box, select the desired VCF file and click Open.

- The Text Import Wizard will appear.

Configure the Text Import Wizard:
- In Step 1, choose Delimited and click Next.

- In Step 2, specify the delimiter used in the VCF file (e.g., Tab and colon) by checking Other.
- Afterward, click Next.

- Proceed to Step 3 to adjust column data formats.
- Finally, click Finish.

Result:
- The Excel worksheet will be populated with the VCF file data.
- The below picture shows the outcome.

Method 2 – Power Query Editor

Access Power Query Editor:
- Go to the Data tab.
- In the Get & Transform Data section, click From Text/CSV.

Import the VCF File:
- The Import Data dialog box will appear.
- Select the desired VCF file and click Import.

- Another dialog box will display the file content.

Adjust Column Structure:
- Double-click the file to open it.

- Note that all values are in a single column.
- To modify this, select the entire column.
- Under the Transform tab, click Text Column > Split Column > By Delimiter.

- Choose the delimiter (e.g., Tab) and click OK.

Load Data into Excel:
- Go to the Home tab and click Close & Load.

- The VCF data will now appear in the Excel worksheet.

Download Practice Workbook

You can download the practice workbook from here:

Open VCF File.xlsx

<< Go Back to Import vCard to Excel | Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub ImportVCFRecordWise() Dim wb As Workbook Dim ws, wsTemp As Worksheet Dim filePath As String Dim fileDialog As fileDialog Set wsTemp = Sheets.Add(After:=ActiveSheet) wsTemp.Name = "Temp" Set fileDialog = Application.fileDialog(msoFileDialogFilePicker) fileDialog.Filters.Clear fileDialog.Filters.Add "VCF Files", "*.vcf" If fileDialog.Show = -1 Then filePath = fileDialog.SelectedItems(1) Set wb = Workbooks.Open(Filename:=filePath) With wb.Sheets(1) .UsedRange.Copy wsTemp.Range("A1") End With wb.Close SaveChanges:=False End If Call ExtractData For Each ws In ThisWorkbook.Sheets If ws.Name = "Temp" Then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True Exit For End If Next ws Set fileDialog = Nothing End Sub Sub ExtractData() Dim ws As Worksheet Dim outputRow As Long Dim lastRow As Long Dim outputSheet As Worksheet Set ws = ThisWorkbook.Sheets("Temp") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Set outputSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) outputSheet.Name = "Output" outputSheet.Range("A1").Value = ws.Range("A2").Value outputSheet.Range("B1").Value = ws.Range("A3").Value outputSheet.Range("C1").Value = ws.Range("A4").Value outputSheet.Range("D1").Value = ws.Range("A5").Value outputSheet.Range("E1").Value = ws.Range("A6").Value outputSheet.Range("F1").Value = ws.Range("A7").Value outputSheet.Range("G1").Value = ws.Range("A8").Value outputRow = 2 Dim vcardData As Variant vcardData = ws.Range("A1:B" & lastRow).Value Dim i As Long For i = 1 To UBound(vcardData, 1) If vcardData(i, 1) = "BEGIN" And vcardData(i, 2) = "VCARD" Then outputSheet.Range("A" & outputRow).Value = vcardData(i + 1, 2) outputSheet.Range("B" & outputRow).Value = vcardData(i + 2, 2) outputSheet.Range("C" & outputRow).Value = vcardData(i + 3, 2) outputSheet.Range("D" & outputRow).Value = vcardData(i + 4, 2) outputSheet.Range("E" & outputRow).Value = vcardData(i + 5, 2) outputSheet.Range("F" & outputRow).Value = vcardData(i + 6, 2) outputSheet.Range("G" & outputRow).Value = vcardData(i + 7, 2) outputRow = outputRow + 1 End If Next i outputSheet.UsedRange.EntireColumn.AutoFit End Sub

2 Comments

Anand Jun 26, 2023 at 6:30 PM

I am able to import vcf file into excel as shown above. I want each record to be a row , sothat the whole vcf file becomes an excel worksheet which I can modify column by column. How can that be done ?

Lutfor Rahman Shimanto Jul 2, 2023 at 3:58 PM

Hello ANAND,

Thanks for reaching out and posting your interesting issue. The requirement you mentioned can be done with the help of an Excel VBA code. The code contains two sub-procedures named ImportVCFRecordWise and ExtractData. All you have to do is to run the ImportVCFRecordWise procedure to achieve your goal.

Excel VBA Code:

Solution Workbook: Download the Workbook used to solve the issue.
DOWNLOAD WORKBOOK

VCF File: The VCF file contains some raw data. I am using the data described in this article to be more specific.

Steps:

Press Alt+F11 >> insert the mentioned code >> press F5 or click on Run.

Choose the intended VCF file >> click on OK.

As a result, we will see an output like the following one.

Things to Keep in Mind:

1. You have to run only the ImportVCFRecordWise procedure.

2. You must delete the Output sheet if it exists before running the code.

This concept will assist you in reaching your goal. Good luck!

Regards
Lutfor Rahman Shimanto

How to Open VCF Files in Excel (2 Methods)

Method 1 – Text Import Wizard

Method 2 – Power Query Editor

Related Articles

Leave a reply Cancel reply