Export iPhone contacts to Excel using this free template

Posted on December 2nd, 2014 in Excel Howtos , Learn Excel , Templates - 23 comments

Export iPhone contacts to Excel - FREE Template

Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.

After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file. It has my phone numbers in this format:

Example VCF file format - how to export iPhone contacts to Excel

Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template for that.

Template for exporting iPhone contacts to Excel

As a first step, download the template.

This template can,

  • Export iPhone contacts to Excel
  • Create iPhone import format from a list of names & phone numbers in Excel

Exporting contacts from iPhone to Excel

To export the contacts from your iPhone to Excel, follow below steps

  1. First back up all the contacts on your phone to iCloud
  2. Now, visit iCloud and select all of your contacts.
  3. Using the settings gear icon at the bottom, export your contacts to a .VCF file.
  4. Open the vcf file in notepad & copy everything.
  5. Paste the data in Data column of “export” tab of the download file.
  6. Names & phone numbers will be extracted in column D:J
  7. Filter the table so no blanks are shown in Name column
  8. Copy the values from Name & phone number columns and paste in a separate sheet or file
  9. Save.

Importing spreadsheet contacts to iPhone

To copy all your spreadsheet contacts to iPhone,

  1. Go to “Import” tab of the download file.
  2. Type or paste your contact information in the columns B,C & D.
  3. Select “VCF to copy” range (from H4 to last cell)
  4. Copy
  5. Open notepad and paste.
  6. Save the notepad file as contacts.vcf
  7. Import the VCF file to your iCloud
  8. Done

Confused about the process? See this video

Since the process of exporting or importing contacts thru iCloud can be a little confusing, I made a small video explaining how the template works. See it below:
(click here to see the video on our YouTube channel)

 

How does the template work?

The vCard format files are simple text files. So when pasted in Excel, all we need to do is figure out where the contact name & phone numbers are and extract them using, what else… Excel formulas.

Exporting VCF to Excel:

You can examine all these formulas by unhiding columns C & K:Q in the export tab of the template.

Importing Excel data to VCF:

  • This uses INDEX formula to get a name & phone number from entered data.
  • Then uses CHOOSE & SUBSTITUTE formulas to create the corresponding VCF lines
  • Finally TODAY & NOW formulas to create the timestamp element of the VCF

You can examine these formulas in columns F,G & H in the import tab.

Do you like this template?

It was fun building something useful & immediate like this in Excel. Although, soon after I created the template, my iPhone magically sprung back to life, I will be ready next time I need to look at my contacts or load them to another phone.

How do you like this template? Would you use this or some other app to export / import your contacts? Please share your thoughts and tips using comments.

Written by Chandoo
Tags: , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

23 Responses to “Export iPhone contacts to Excel using this free template”

  1. Vad says:

    Any operations on text is to find the pattern which is the tricky part of the problem and next is apply Excel function which is easier part.
    Most of the encoded messages in any system will definitely follow a pattern. I have always used this technique to solve my problems with Excel without downloading the small app developed by others for doing the same.
    My story of finding the last word in a sentence is at vppc.wordpress.com
    Though I hate to publicize but do not have patience to type all πŸ˜€

  2. Huy Pham says:

    Is this possible with Android devices?

    • Chandoo says:

      It should be. The VCF format is universal. If you can somehow extract the contacts from Google or other platforms on which they are saved by your Andriod device, you can follow the same steps.

    • Chris says:

      You can export your Android phone contacts from the Google contacts website assuming you have not disabled Google sync on your phone. You get a three choices for the file format - Google CSV, Outlook CSV, and VCF. Choose the Outlook CSV format and open with Excel. It's very easy. If you don't use a Google account on your Android phone, then this won't help.

  3. Oficientes says:

    Since Android does not work, I just did the test, perhaps VCF formats are different.

    PS: Sorry for mi bad english!

  4. Deborah says:

    Thank you for this! It's awesome!

  5. Chris says:

    I just tried this. I followed the instructions perfectly (I think).
    I downloaded all my contacts from the icloud in vcf format and saved the file.
    I opened the file in notepad and copied and pasted the data into the data in Data column of β€œexport” tab data column (8110 lines of data)

    Nothing got extracted into Columns D-J

    Now that being said, when I downloaded the template; I got the following error on opening: The file you are trying to open is in a different format than specified by the file extension. (The file extension is xls). I change the extension to .xlsx and the file opens without an error. But still does not do the extract. πŸ™

    • Chandoo says:

      Hi Chris,

      Can you first record of your VCF file in comments so that I can examine what is going on. Please post everything between first BEGIN:VCARD and END:VCARD lines. Please replace names & numbers with random data to ensure privacy.

      • Abhijit says:

        I am facing the same issue. Here is the first entry:

        BEGIN:VCARD
        VERSION:3.0
        PRODID:-//Apple Inc.//iOS 7.1.1//EN
        N:;A;;;
        FN:A
        TEL;type=CELL;type=VOICE;type=pref:123
        REV:2014-06-14T09:29:51Z
        END:VCARD

  6. John H says:

    I think Mr Modi should have made the big names in your template!

    thanks for this post - I also have an iphone 4 on borrowed time.

  7. Denice says:

    I downloaded the template, followed the instructions and bingo.
    Thank you so much Chandoo.

  8. Diana says:

    BLESS YOU CHANDOO! I upgraded my iPhone to a 5S and gave my old 4 to my daughter. I went to switch the account on the old 4 to her account, but I had forgotten her Apple ID password so I figured "I'll fix that later." Several weeks passed and little did I know, she was deleting the contacts on the phone because she didn't need them. Until she came to me and said "you know too many people" I had no idea that she was not only deleting them on her phone, but they were disappearing from my new 5S (thank you iCloud sync) since both phones were connecting to the same iCloud account. After spending $65 on a product to extract contacts from an old backup, I still had no idea how to get the contacts onto my new phone. The extracted contacts are in individual text files. Looks like I'll be writing a macro to import them into your template and then I'll be ready for import to my new phone. Thanks again for this!! I can't wait to try it out this weekend.

  9. charles says:

    Howdy,
    Worked pretty well but most of the numbers are missing. Thoughts?

  10. David says:

    Hi

    Slightly tangental question, but wondered if you had come across this -

    I have all my contacts in Excel (extracted from outlook and tidied) and then imported into google contacts

    the problem i have is in the formatting of some (not all numbers) particularly international ones.

    eg + 31 61234567 loses the "+" in excel and then comes out as 3.16E+11 when up loaded to google

    is there a way of formatting the international numbers so that the prefix (eg +31) is retained ??

    many thanks!

  11. Chris H says:

    Hello Chandoo,

    I used this template and it worked like you said, but it duplicated the last name twice. It appears that your template is only capturing the phone numbers (which is obviously what you wrote it for), is it possible to expand this template to capture mailing addresses? My goal here is that I would like to create a lookup table in excel to do a mail merge for envelope printing. I keep all of my contacts in my mac.

  12. Sunny says:

    Thanks so much. You are a lifesaver. I have been trying to figure this out for 2 weeks and found no help from APPLE. They even suggested that I check elsewhere! I will come to you from now on for great - easy ways that even I can get things done without unwarranted spending.

    Thanks again,
    Sunny

  13. Shawn says:

    Could this work with Mac's "numbers" program? Is there a "numbers"
    version of this?
    Shawn

  14. HarryIphone says:

    Hi Chandoo

    I got some #values coz my vcard export looks like this (Iphone 4s). I had to hack to get it to work.

    BEGIN:VCARD
    VERSION:3.0
    PRODID:-//Apple Inc.//iOS 6.0.1//EN
    N:;Test;;;
    FN:Test
    TEL;type=CELL;type=VOICE;type=pref:+1XXXXXXXXXX
    TEL;type=WORK;type=VOICE:+1XXXXXXXXXXX
    REV:2012-12-17T21:01:31Z
    END:VCARD
    BEGIN:VCARD

    The second comment is that ur template takes in only one number but a lot of my contacts have a second or third phone... Any suggestions for that?

  15. Mannan says:

    Hey Chandoo,
    Is the file still linked? I can't seem to download the excel itself. Thanks for your help mate.
    cheers.

  16. Alex says:

    Hi Chandoo

    This template would be a Godsend if I can get it to work...

    When I paste into the column, it says "Data on the Clipboard is not the same size as the selected area, do you still want to proceed?" I select yes, and then after it pastes, everything is in the first column and no phone numbers or any other data show up.

    If you can offer any assistance I would be extremely grateful.

    • Alex says:

      Just a side note: The same thing happens when I paste it the right way (meaning that now when I do it, Excel does not give me that message and everything seemingly goes smoothly with the paste-in but still no data in any of the other columns)

Leave a Reply