Forum Discussion

Jerykro's avatar
Jerykro
Copper Contributor
Jan 12, 2024

Looking for help

  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 fr...
excel
Jerykro's avatar
Jerykro
Copper Contributor
Jan 13, 2024
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
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jan 14, 2024

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.