misaligned columns

P

pattmatters13

New Member
Joined
Jul 5, 2016
Messages
3
This forum has been an enormous help in the past with excel questions, but this is my first time posting.

I'm posting because I've created several large lists of contacts but the data is not uniform which has resulted in enormous amounts of misalignment. An example of this would be an email address in column C, but on another row, the email address is in column E.

Getting ahold of a better formatted source file is not an option as these lists were created manually, and they took forever. A short example is below.

I've tried creating various equations to pull a cell if it contains a specific item (ie "@", for email address) but am having a hard time making progress otherwise.

How would you recommend I reformat/reorganize this data?

I was questioning whether or not to post the below data, but is is already publicly available at the Department of Real Estate's website.

Thank you in advance!

Debra Sundve1-2-1 Realty2950 E. Lexington AveGilbert, AZ 85234Home - (480) 688-4900Mobile - (480) 688-4900Board: SEVRAR121realty@cox.netLicense # BR110899000121realty@cox.net
Debra M ThomasAvenuewest Phoenix20 E Thomas Rd Ste 2200Phoenix, AZ 85012Office - (602) 326-3917602-326-3917 -Fax - (602) 635-4611Other Phone - (602) 753-2412Home - (602) 326-3917122103@gmail.comMobile - (602) 326-3917Board: PAR122103@gmail.comLicense # BR518377000
Amanda PittsAmerican Scene Real Estate2818 W Northern AvePhoenix, AZ 85051-4298Fax - (602) 864-0045Mobile - (602) 391-3297Board: PAR1belair1963@gmail.comLicense # SA6524440001belair1963@gmail.com
Martin O'MalleyArizona Best Home, Inc2709 Leisure WordMesa, AZ 85206Office Phone - (602) 574-8816Fax - (866) 679-5837Home - (602) 574-8816Mobile - (602) 574-8816Board: SEVRAR1modive@gmail.com1modive@gmail.comLicense # BR530814000
Chuck Shaughnessy1st Insight Inspections4809 E Thistle Landing#100Phoenix, AZ 85048Office Phone - (602) 625-6644Board: SEVRAR1st.insight@gmail.com1st.insight@gmail.comczarinav@me.comLicense # SA536559000
Rachel Murphy1st Home Sold Realty4941 S Vista PlChandler, AZ 85248Fax - (877) 308-1167Home - (480) 650-6213Mobile - (480) 650-6213Board: SEVRAR1sthomesold@gmail.com1sthomesold@gmail.com
Tim Sponsler1st Priority Inspections832 N. Grace St.Scottsdale, AZ 85257Fax - (480) 459-5707Home - (480) 219-7663Mobile - (480) 277-2772Board: SEVRAR1stpriorityinspections@cox.net1stpriorityinspections@cox.netBoard: WEMARLicense # SA630364000
Bruce LopezAmerican Realty Brokers4001 North 32nd StreetPhoenix, AZ 85018Fax - (602) 340-1228Home - (602) 953-9111Mobile - (602) 617-0596Board: Scottsdale2brucelopez@gmail.com2brucelopez@gmail.comLicense # SA578243000

<tbody>
</tbody>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Ok, so first, a macro/VBA would probably work more autonomously than this option, but I'm not well versed in those. Here's what I do know:
Use this formula in the next blank column you have: =INDEX(A3:P3,MATCH(TRUE,NOT(ISERROR(SEARCH("@",A3:P3))),0))

Change the row number if need be; This is just based off a quick copy & paste of your information provided so I could test & see if it worked. For me, this landed in cell Q3. It will search the row it's in, and pull the email. The formula can be changed to find "-" for the phone numbers, and "#" for the license numbers, in the next columns to the right. Just replace the section with the @ symbol.

When you paste this formula you will have to press Ctrl + Shift + Enter for it to calculate since it's an array. If you just hit Enter, it will display #NA. Once the first cell is pasted and brings over the information you want, highlight the cell (only the bold cell outline, not the flashing lines like when you copy), put your mouse on the tiny square that will appear at the bottom right corner of the cell, then drag that all the way down to the end of (or beyond) your last row, hit Enter. That will bring the formula down all rows. Do the same for any other columns you create.
Once the information is put into new columns, you can just hide the array columns to look like a smooth table, or create a pivot table, or move the formulas into a new sheet & keep Sheet1 as a raw data page to pull from in the future if you'll continue adding to it.

This will create a new table for the data, filtering it into whichever order you set it up. This is really just a work around and there may be a better macro/vba solution out there, but I hope this can help in the mean time.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,836
Messages
6,127,182
Members
449,368
Latest member
JayHo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top