Mac

Ask Mac Questions and Get Answers from Experts ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Mac

1. I opened a Mac Address Book file in Excel. 2. The data is

Customer Question
not in columns. 3. The...
1. I opened a Mac Address Book file in Excel. 2. The data is not in columns. 3. The spreadsheet contains data that i don't want. 4. There are over 3,900 records taking up to 8 rows, depending on what users have entered over the years. 5. I have spent hours going through row by row, moving first and last name into columns (on a single row) and then moving the email address to its column in the same row as the names. BIG QUESTION: IS THERE A FASTER WAY TO REMOVE ALL THE UNWANTED DATA? Hope you can help. Gai
Submitted: 5 years ago.Category: Mac
Show More
Show Less
Ask Your Own Mac Question
Answered in 4 minutes by:
3/15/2012
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago
John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
Experience: Worked on Macs and PC's for 20+ years.
Verified

Hi,

 

Yes if there is a logic in the way the address lines are entered. Please send me the file so I can check the fields. To do so go to http://www.wikisend.com/ and upload the file (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

 

If the file has sensitive information let me know before you upload it

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
Hi, John D.
Sadly, there really isn't any logic. There is a template for each address (a vcard in Address Book), but not all fields are used and users can easily add more fields and even change field names.
I hoped there would be a way I could remove all of the ROWS that have ADDR or TEL (and others) as the first letters, but haven't been able to work out a way to do that. Was hoping for a version of search & replace that would delete the entire row.
Gai
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

It's ok. Please send me the file and I will try to find a pattern

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
John D,
I keep getting the message" We are sorry, but an error has occured while uploading.

You can return to the start page and try again.

In the meantime, do you know of a way I can download my Address Book vCard/archive in COLUMNS? Earlier this week, I was instructed to save the archive (.abbu) to a flash drive, and open it in Excel from my Windows computer. Mac help desk advised that it is not possible to convert .abbu files to either Excel or .csv.
So I successfully opened the Mac .abbu file and saved it as both Excel and .csv. But data is spread all over the place like a dog's breakfast!
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Ok let's first get file through

 

Ok try uploading it here, then send me the Share URL link

 

http://www.filesharesite.com/

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
http://www.filesharesite.com/files/201203/1331850729Excel_all_address_book_entries.xlsx.html

Let me know that it's ok.
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Got it, thanks

 

Gai, yes I can create a macro that will transpose each address into 13 columns, but since not all the address have the same number of rows the listing may need be in the same columns.

 

e.g. if address 1 has 13 rows, the fields will be placed on anther sheet in columns A thru M, starting in A

 

and if the next address has 10 rows, it will be placed in columns A thru J starting form A

and so on

 

 

Would that be ok

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
You know what, I only need a couple of ROWS for each entity (between BEGIN:CARD and END:CARD) ....
N; (multiple columns for first, last, sometimes middle initial, sometimes a suffix, sometimes a Mr. or Ms. etc.)
EMAIL

Does that make it easier?
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Ok I wrote a macro (and some formulas) and was able to process all 36,000 rows so that each address is transposed into a single a row. Here is a screenshot showing the result of the 50 addresses

 

 

graphic

 

 

Have a look and let me know if it is ok so I can send you the complete list in the Excel file


Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

please refresh this page to see the updated screenshot

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
Hi John D,
Thanks for revised screenshot. It looks as if the last name (in those fields that begin with N: ) hasn't been included.
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Can you give me an example. Which row on your original list?

 

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Ok I see what you mean.

 

I assumed that column A is all titles. But apparently it has some address related data.

 

Ok will fix that

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
It sure would have been nice if the address book data had separated from its fieldnames! Thanks for keeping on this one for me.
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

I will try do that if the field names are XXXXX XXXXX have to create another macro for that

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
No .... I didn't mean for you to do it. I meant it would have been easier if the archive from Address Book was formatted differently.
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

lol.. already wrote that macro and it is now possessing the data...

 

The two macros will take some 20-30 mins to complete transposing and changing all the data

 

Will get back to you as soon as I am done

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
You are amazing. Thank you.
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Whew!!

 

It is taking a lot of time processing all the data. In the meantime here is the initial list after running two macros, 10 sets of formulas, and plenty of manual modifications. It may not be perfect but I think it is as close it can get with the given time and scope

 

Attachment: 2012-03-16_023725_output_test.xlsx

 

Hope this is ok. Let me know what know what you think

 

 

 

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
Hello John D, Thank you for all the work you are putting into my plea for help.

Sorry I didn't reply last night. I'd changed my email to my home address, and kept checking there. obviously that change didn't happen and this morning we reconnected via work email.

I appreciate your efforts, but the report sample can't be uploaded to iContact.
I only need the names and the email data for each entity. This information is in the file I sent yesterday:

the name fields are identified by N:aaaaaaaaa in the cell in column A.

the email fields are in another row that always contains EMAIL in column A.

Could you extract just that data and use it to create a new, smaller table? For example:

For each contact, a single record begins where BEGIN:VCARD is in column A and a record ends where END:VCARD is in column A.

1. where the text in the cell in column A = N:aaaaaaaaaa (the last name appears after N:)
can you extract the first five cells (A:E) and paste them in a row in columns A:E a new report?

2. where the text in the cell in column A = EMAIL
can you extract the first three cells (A:C) and past them in the same row, in columns F:H in the new report?
JOHN, NOT EVERY CONTACT HAS A ROW FOR EMAIL. So just go to the next contact, please.

I work two jobs and need to leave for my second job in a few minutes. I will not have access to a computer. If you have questions before 2 pm, can you call or text me? XXX-XXX-XXXX.

Thank you again for looking for a solution.
Sincerely,
Gai
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Hi Gai,

 

Not sure how else to send you the file (we cannot use email on this site). Will try other methods. In the meantime here is what the final list looks like

 

graphic

Ask Your Own Mac Question
Customer reply replied 5 years ago
Hello again.
There is a text message option and I just signed up.

John, early in our conversation I stated that I wanted to remove most of the rows. You have made a report out of the huge amount of data, but I still can't use it.
Please let me know whether you can remove all the data I do not need, to create a much smaller report.

Thanks, Gai
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Yes I think I can do that, but first let's get this one accepted which is per your original instructions so I can get some credit for my time on this rather extended question

 

Thank you

 

 

 

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
Experience: Worked on Macs and PC's for 20+ years.
Verified
John D and 87 other Mac Specialists are ready to help you
Ask your own question now
Customer reply replied 5 years ago
Hi John
I'm at my office job now. I don't want to get into a debate and certainly an aware of the fact that you've spent a lot of time figuring out ways to sort the data. So I will hit the accept button for you.

Will you keep online or send me some instructions?

I want to upload the data to iContact this weekend. The format has to be: names and email addresses on one row per contact. Columns A:E for name and F:G for email (if there is one).

Thanks again,
Gai
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Yes Gai I will be online most of the time today

 

Thanks

 

 

 

 

 

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Thanks Gai. Much appreciated.

 

I will start working on the name/email address shortly. Will get back to you as soon as I am done

 

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
THANK YOU!
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

no prob

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

For example in row 24, the formula in FT24 counts the number of cells in the range E24 thru FM24 that are larger that C24

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
John ... is this question for another client?
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Oops!. You are correct.

 

Please ignore it, and thanks for letting me know

 

I should have the list ready in about 15 mins

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
W O W !
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Ok here is the file with the names and email for all the addresses. Please note that some names have more than one email address associated with them

 

http://www.filesharesite.com/files/201203/1331933352output1.xlsx.html

 

Please check it and let me know if it needs and adjustments

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
Hi John,
You are nearly there!

I still need the names separated for uploading to iContact and addressing some messages to first name.

For every row where column A = N:aaaaa (aaaaa is supposed to be the contact's last name)

can you extract the first five cells (A:E) and insert them into columns A:E the new report?
Sometimes there will only be one populated cell (in column A)--other cases use all five cells.
I will have to do some manual cleaning up, because there is no consistency.

Thanks,
Gai
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Gai, I am not sure I fully understand. Are you referring to the original data?. If so the final list is so far removed from that data, some 8 steps have been performed to transpose the data and extract the names and emails (the rows in the original list no more relate to the rows the final transposed and condensed list)

 

If it helps I can extract the first names and the last names from the last list and put them in separate columns

 

Let me know

 

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

UPDATE:

 

Ok I managed to get all the names that are preceded by the "N:" character. They are placed in column A in this file

 

Attachment: 2012-03-16_230315_output2.xlsx

 

Hope this is ok

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
Thank you John.

I still need to have first names in their own column.
Can it be done?
If not, I will have to manually remove the last name and comma before the firstname and remove the comma and salutation from after the firstname.

Thank you very much for continuing to work on this today.

Gai
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Ok one moment please...

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

Had to do most of extraction manually because of the inconsistency of the data

 

Here if the final list

 

 

Attachment: 2012-03-17_003813_output3.xlsx

 

Hope this helps

 

Thank you

 

 

Ask Your Own Mac Question
Customer reply replied 5 years ago
Hello John,
My huge thanks for creating this report. This final report is exactly what I wanted.

What would you suggest I do differently next time, so I don't take up so much of your time/the expert's time? I thought my request was clear, but I accept the fact that you did not understand what I wanted.

Again, thank you for sticking with this on my behalf.
Sincerely,
Gai
Mac Support Specialist: John D, BS Degree in Engineering replied 5 years ago

You're welcome Gai! Glad you are happy with the result

 

Ask Your Own Mac Question
Ask John D Your Own Question
John D
John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
9,666 Satisfied Customers
Experience: Worked on Macs and PC's for 20+ years.

John D is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill

Bill M.Schenectady, New York

The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you!

FrankCanada

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

< Previous | Next >

Meet the Experts:

Mike

Mike

Mac Medic

8,552 satisfied customers

Over 20 years IT experience with Apple computers in publishing, marketing and design.

Daniel

Daniel

Mac Genius

4,711 satisfied customers

Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.

Vinod Menon

Vinod Menon

Support Specialist

2,370 satisfied customers

worked as a Tech support Associate for Apple products

John T. F.

John T. F.

Mac Druid

1,979 satisfied customers

20+ years in the computer/Mac industry

David

David

Mac Support Specialist

1,468 satisfied customers

BSc H.Dip Apple Certified Professional

Claws224

Claws224

IEEE Datacommunication Engineer

965 satisfied customers

IEEE, Microsoft

Mac Tech

Mac Tech

Apple Specialist

888 satisfied customers

7 + Years Supporting Apple Products

< Previous | Next >

Related Mac Questions
I cannot open Excel on my iMac. Os El Capitan X 10.11-6.
I WAS AWAY ALL LAST WEEK. TURNED OFF MY COMPUTER AND WHEN I RETURNED EXCEL NO LONGER RESPONDED… read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,207 satisfied customers
Im on my moms computer trying to help her. Her excel program
Im on my moms computer trying to help her. Her excel program is not working and will not open at all. I have the original Office.mac dvd but dont know how to reinstall excel without losing whatever da… read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
My microsoft excel froze with the SWOD. Mac os sierra. A
my microsoft excel froze with the SWOD JA: What's the model and Operating System (OS) of your device? Customer: mac os sierra JA: When did you purchase your Apple device? What troubleshooting have you… read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I can't open excel on my Mac. This is a MacBook Pro Retina
This is a MacBook Pro Retina 2012 and operating macOS Sierra … read more
Jason Jones
Jason Jones
AAS Information Technology
6,316 satisfied customers
I can't dictate into excel with dragon Pre 15. Dell. Few
I can't dictate into excel with dragon Pre 15 JA: What's the make and model of your computer? And what version of Excel are you using? Customer: dell JA: Have you installed any updates recently? Custo… read more
Justin Johnson
Justin Johnson
Training and Development Contractor
484 satisfied customers
I have been using Excel for years now. In the last few
I have been using Excel for years now. In the last few weeks, whenever I input information and hit the "enter" key, the line I entered information on turns black. The info is there but not visible unl… read more
Kris R
Kris R
IT Manager
Computing
2,257 satisfied customers
Hanging with Word and Excel - just did updates. No idea. It
It is a Mac mini, i7 with 16 mb ram purchased in 2013, it should be running the latest Mac OS … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I tried to download Adobe Export PDf to save in excel
I tried to download Adobe Export PDf to save in excel format. I signed up for the 1.99/mo though it didn't let me download and as I try to do it again it says I have already done it and can't do it ag… read more
Kamil Anwar
Kamil Anwar
IT Consultant / Systems Engineer
Bachelor\u0027s Degree
426 satisfied customers
When using excel it keeps reexing and I get the littl
when using excel it keeps reexing and I get the littl spinnng wheel what can i do to combat this … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,207 satisfied customers
Excel for Mac. The tabs at the bottom of the page have
Excel for Mac. The tabs at the bottom of the page have disappeared. Tried changing preferences as suggested and no change.Thanks.… read more
Jon
Jon
Bachelors Degree
377 satisfied customers
I need the excel text converter for mail merge on an old
I need the excel text converter for mail merge on an old macbook pro … read more
Russell H.
Russell H.
computer technology consultant
Vocational, Technical or Trade Scho
775 satisfied customers
I am getting this error in my Mac word and excel Microsoft
I am getting this error in my Mac word and excel Microsoft Error Reporting log version: 2.0Error Signature: Exception: EXC_BAD_ACCESS Date/Time: 2017-08-23 02:58:26 +0000 Application Name: Microsoft W… read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I cannot see tabs in an excel document, its on cloud, 1 full
i cannot see tabs in an excel document JA: When did you last update Excel? Customer: its on cloud JA: What have you tried so far with your Excel? Customer: 1 full screen 2. tiled in window view JA: An… read more
The-PC-Guy
The-PC-Guy
Bachelor\u0027s Degree
628 satisfied customers
I have the 2004 version of Excel & Word for MAC. If I
I have the 2004 version of Excel & Word for MAC. If I upgrade to 2016, will my spreadsheets work seamlessly? Thank you. … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
My excel is freaking out and opening many random files,
my excel is freaking out and opening many random files … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I have a Mac desktop with microsoft word and excel
I have a Mac desktop with microsoft word and excel applications. The document I wish to open in excel keeps giving me a not responding message. … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
Send Mac Basics: Address Book. Using OS Sierra 10.12.4. I
please send Mac Basics: Address Book. Using OS Sierra 10.12.4 … read more
Mike
Mike
Mac Medic
Some college
8,552 satisfied customers
My address book will not open - showing 1712 what should I
Hello, my address book will not open - showing 1712 what should I do? … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x