Looking for help

[Jerykro]

HI,

I’m a resident of a 600 “home owner association” (HOA). WE want to communicate with the owners. WE have a book with our residents listed. We would like to get the names and Email addresses from the book into a mailing program. Can you help us with an Excel programing? Please check the links attached for a sample of the type of pages that need conversion.

Jerry Kroot

email address removed for privacy reasons

https://docs.google.com/spreadsheets/d/1GGTKMWIFfFZ08vZf0RTlsb398R1Jk6D1/edit?usp=drive_link&ouid=11..., https://docs.google.com/spreadsheets/d/1iwYVQnllngB9fmsbgT1eQJ1Us9NouNVh/edit?usp=drive_link&ouid=11...

[#2]

@Jerykro 

I can try to guide you on how you might approach this task. Here is a step-by-step guide:

1. Open Excel Workbook: Open the Excel workbook that contains the list of residents and their email addresses.
2. Format Data: Ensure that your data is organized with clear headers. You should have columns for "Name" and "Email Address."
3. Export Data to CSV:
• Save your Excel file as a CSV (Comma Separated Values) file.
• Go to "File" -> "Save As."
• Choose a location.
• In the "Save as type" dropdown, select "CSV (Comma delimited) (*.csv)."
• Click "Save."
4. Prepare for Mail Merge:
• Open your mailing program (e.g., Microsoft Word for a Word mail merge or your email client).
• Start a new document or email.
• Look for the "Mailings" or "Mail Merge" tab.
5. Insert Merge Fields:
• In Word, go to the "Mailings" tab and select "Start Mail Merge" -> "Email Messages."
• Select "Select Recipients" -> "Use an Existing List."
• Browse and select your CSV file.
• Insert merge fields like <<Name>> and <<EmailAddress>> where you want them in your message.
6. Complete the Mail Merge:
• Complete the mail merge process according to the steps in your specific program.
• Preview the results to ensure the merge fields are working correctly.
• Complete the merge, sending emails or creating documents for each resident.

The text and steps were edited with the help of AI. The exact steps for importing into your mailing program may vary based on the program you are using. Typically, mailing programs allow you to import contacts from a CSV file. Please refer to the documentation or support resources for your specific mailing program for detailed instructions on importing contacts.

Your link cannot be opened from my site, does not work for me.

This process allows you to use the data in your Excel workbook to personalize emails or documents for each resident. The specifics might vary slightly depending on your mailing program, so check the documentation for your specific application for more detailed instructions.

If these steps not helped you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc. In this link you will find some more information about it: Welcome to your Excel discussion space!

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

Thank you for the information. It will be very valuable, when I get to that point. My bigger problem as follows:
I got the spread sheet from a 90 page Home Owners Association (HOA)(600 homes) listing of residence. I scanned the page on a printer that converted it to a PDF file format. I then converted the PDF to Excel format.
That gave me a one column spread sheet that had the following information in it, each one occupies one line:
Husband’s and wife’s name,
Address,
Second address,
E-mail address.
Hears where the rub comes in:
Each cell in the row can have one to four of the above items in it, although all the information is there in order.
I need someone to write an Excel formula that would give me name and E-mail address each in their own column.

Jerry Kroot

[#4]

That gave me a one column spread sheet that had the following information in it, each one occupies one line:
Husband’s and wife’s name,
Address,
Second address,
E-mail address.
https://techcommunity.microsoft.com/t5/excel/how-to-extract-unstructured-data-into-excel/m-p/3900593

looks like data clean.

I can not open
https://docs.google.com/spreadsheets/d/1GGTKMWIFfFZ08vZf0RTlsb398R1Jk6D1/edit?usp=drive_link&ouid=11...
because of the network.


Can you post some data or upload file
here?
Regular expression may works here.

[#5]

@peiyezhu 

[#6]

@Jerykro 

Handling a single column with varied data in each cell can be a bit tricky. However, if the information is consistently formatted within each cell (for example, if each person's name is followed by their email address), you might be able to use Excel formulas to extract the desired information. Here's a general approach:

Assumptions:

• Each cell contains either the husband's name, wife's name, address, second address, or email address.
• The names are listed first in the cell, followed by the email address.

Steps:

Splitting Husband's and Wife's Name:

1. Husband's Name:

=IFERROR(LEFT(A1, SEARCH(" and ", A1) - 1), A1)

This formula extracts the part before " and " (if present), assuming the husband's name comes first.

1. Wife's Name:

=IFERROR(MID(A1, SEARCH(" and ", A1) + 5, SEARCH(",", A1) - SEARCH(" and ", A1) - 5), "")

This formula extracts the part between " and " and "," (if present), assuming the wife's name comes second.

Extracting Email Address:

=IFERROR(MID(A1, SEARCH("@", A1) - FIND(" ",A1,SEARCH("@",A1)), SEARCH(" ", A1, SEARCH("@", A1)) + SEARCH(" ", MID(A1, SEARCH("@", A1), LEN(A1))) - 1), "")

This formula attempts to extract the email address. It looks for the "@" symbol and then captures the characters between the preceding space and the following space.

Instructions:

1. Copy the formulas into separate columns next to your original data.
2. Adjust the cell references (A1) based on the location of your data.
3. Copy the formulas down for the entire column.

Important Notes:

• These formulas are designed based on the assumption mentioned earlier. If the data varies significantly, the formulas might need adjustments.
• The IFERROR function is used to handle cases where the expected patterns are not found. The text, steps and functions were created with the help of AI.

Hope I was able to help you with this information.