Mail merge format needs to be cleaned

C

cduval

New Member
Joined
Jun 18, 2015
Messages
23
I have an incredibly messy data set that I got from copying and pasting from what looks like a mail merge in word.
Essentially I need to find the data and populate into this columns:
First Name Last Name Address City State Zip Phone Email etc
The data looks like this:
MRS ROBERTA S CHAPIN TTEEMR R SPENCER ALLEN TTEEMR HARRIS E TULCHIN
FBO CHAPIN FAMILY MARITAL TRR SPENCER ALLEN Q-TIP TRUSTSCHAFER CULLEN MGT
UA DTD 07/08/80UA DTD 04/16/96548 9TH ST
19191 HARVARD AVE # 335541 GRACE DRSANTA MONICA CA 90402-2802
IRVINE CA 92612-4670MENLO PARK CA 94025-572405 09 08
05 09 0805 10 041HM310-451-4300 2BU310-419-7979
1HM949-856-9007 2BU1HM650-323-6784 2BU650-369-2995 223FX 4CE
3FX 4CE3FX650-366-9578 4CE
ALFONSO VASQUEZ JR, MARGARETMR JOEL GOLDSTEIN TTEE, DAVIDMR ALEC MILOSLAVSKY
HELEN VASQUEZ CO-TTEES,OBRIEN TRUST UA DTD 04/03/03,MS GALINA MILOSLAVSKY
ALFONSO & MARGARET VASQUEZFBO GAIL HEISERCOMMUNITY PROPERTY
FAMILY TRUST UA DTD 05/07/03C/O MSINUVEEN
PO BOX 7389229 W SUNSET BLVD STE 71020 BOROUGHWOOD PL
BONSALL CA 92003-0738LOS ANGELES CA 90069-3407HILLSBOROUGH CA 94010-6713
05 10 0706 10 0909 13 12
1HM760-758-9709 2BU310-323-07431BU310/820-1308 2BU1HM650-344-1178 2BU
3FX 4CE3FX 4CE3FX 4CE
MR JOSEPH DLUGOKECKI, MRS RITAMRS NATALIE C VOGELSANG &MR PETER J BLACK TTEE
L GROSS-DLUGOKECKI CO-TTEES,MR MICHAEL L VOGELSANG JT TENPETER J BLACK TRUST
DLUGOKECKI FAMILY TRUST UA DTD6831 BEVIS AVEUA DTD 12/18/00
1/5/2000VAN NUYS CA 91405-38432961 1ST AVE
340 HUELVA CT05 07 06SAN DIEGO CA 92103-5906
OCEANSIDE CA 92057-84041HM818-901-9768 2BU323-642-527805 11 10
06 11 103FX 4CE1BU858-658-3023 2BU
1HM760-722-7252 2CE760-505-49443FX 4CE
3CE619-838-9620 4HM310-510-8485
MR ROBERT A BOTHMAN, MRSMRS TED VALIDR ROBERT M JACKSON
SHARON YVETTE BOTHMAN CO-TTEESMRS BARBARA VALI CO-TTEESMRS JUDITH JACKSON CO-TTEES
BOTHMAN FAMILY REVOCABLE TRUSTFBO VALI FAMILY TRUSTTHE JACKSON REVOCABLE TRUST
UA DTD 12/28/99, (TCW)UA DTD 03/23/00UA DTD 11/22/91
20385 IRON SPRINGS RD2720 PARK AVE43860 10TH ST W STE 2
LOS GATOS CA 95033-9114LAGUNA BEACH CA 92651-2078LANCASTER CA 93534-4806
07 12 0805 09 0907 10 09
1HM408-353-5021 2BU408-279-22771HM949-497-9440 2BU1HM818/368-0754 2BU661/729-4327
3CA408-639-0525 4FX408-279-22813FX 4CE3FX 4CE
JOHN A ROBINSON, THERESA PMR MENG HSUEH HORUDOLF BRUTOCO MD &
ROBINSON CO-TTEES, ROBINSONMRS SHIN JUNG HO CO-TTEESDIANA BRUTOCO JT TEN
FAMILY TRUST UA DTD 06/06/88,MENG H HO & SHIN JUNG HO RVTRLORD ABBETT
BY WBNA AS SECURED PARTYUA DTD 07/22/0327272 VIEWPOINT CIR
2707 MASON WAY4183 MYSTIC VIEW CTSAN JUAN CAP CA 92675-1545
VISTA CA 92084-1829HAYWARD CA 94542-216705 09 07
05 09 0905 12 051HM949-443-1229 2FX949-443-3734
1HM760-630-0124 2BU858-740-21101BU510-769-0222 2BU3CE949-233-3900 4CE
3FX858-586-1798 4CE858-775-55833FX 4CE
DR HENRY L ABRONSMR DANIEL C LORTIMRS GLORIA J LORTI
DR LI-HSIA WANG CO-TTEES425 VISTA PARADA425 VISTA PARADA
ABRONS/WANG TRUSTNEWPORT BEACH CA 92660-3528NEWPORT BEACH CA 92660-3528
UA DTD 05/28/0307 11 1107 11 11
3030 DEAKIN ST1HM949-759-9058 2FX949-759-48741HM949-759-9058 2FX949-759-4874
BERKELEY CA 94705-19473FX 4CE3FX 4CE
06 09 08
1HM510-848-5765 2FX510-437-4187
3OT510-308-0067 HPAG 4OT510-802-0486 LPAG
MS DONNA LYNN STILLO TTEEMR PETER R JULICHER &MR JEFFREY BLUE
DONNA LYNN STILLO FAMILY TRUSTMRS BETH E JULICHER JT TEN(LORD ABBETT LRG CAP VAL)
UA DTD 04/02/984623 E IMPERIAL VIEW CT7405 PYRAMID PL
(NUVEEN MUNI FIXED)ROCKLIN CA 95677-4493LOS ANGELES CA 90046-1316
4774 PARK ENCINO LN08 11 0906 09 07
ENCINO CA 91436-32791HM916-415-9323 2BU916-554-11601HM323-851-7009 2BU310-865-8088
07 10 103FX 4CE3CA310-283-8526 4BU310-865-8998
1HM818-817-0040 2BU
3FX 4CE
MR MIKE O'SHEA &MR JOHN R STAHR TTEEMR BERNERD C KINGSBURY
MRS SHARON O'SHEA JT TENJOHN R STAHRMRS MYRNA A KINGSBURY CO-TTEES
(PORTFOLIO SERV GRP EQ 50/50)TRUST UA UA DTD 03/19/65THE BERNERD & MYRNA KINGSBURY
5889 WOODGLEN DRSEPARATE PROPERTYFAMILY TRUST UA DTD 08/18/03
AGOURA HILLS CA 91301-46294639 ROXBURY RD4565 GREEN VALLEY RD
05 09 06CORONA DL MAR CA 92625-3128FAIRFIELD CA 94534-1367
1HM818-707-1060 2OT818-608-271807 11 1005 10 09
3FX 4CE1HM949-760-0905 2BU1HM707-864-1481 2FX707-864-9109
3FX 4CE3FX 4CE
MRS MYRNA A KINGSBURY TTEEMR KIM B SNYDERMR LEE M KIM, MRS RUTH L
THE BERNERD & MYRNA KINGSBURY856 ROXBURY DRMINTURN-KIM CO-TTEES, LEE M
FAMILY TRUST UA DTD 04/30/92PASADENA CA 91104-4021KIM AND RUTH MINTURN -KIM
4565 GREEN VALLEY RD05 06 06FAMILY TRUST UA DTD 10/23/97
FAIRFIELD CA 94534-13671HM626-798-7020 2BU626-432-548819172 RED BLUFF DR
05 10 093FX626-432-5489 4CE213-400-0761TRABUCO CYN CA 92679-1112
1HM707-864-1481 2FX707-864-910905 07 06
3FX 4CE1HM949-858-0856 2BU
3FX 4CE
C LAHTI, T SCHLAMME CO-TTEES,MS ANNE PATTERSONMR NICHOLAS L TETI &
S. FAMILY TRUST SEM ACCT, UA(LORD ABBETT LRG CAP TAX BAL)MRS BARBARA B SASSON-TETI
DTD 09/28/9524311 SYLVAN GLEN RDJT TEN
C/O MSICALABASAS CA 91302-23622809 HOLLY RD
9229 W SUNSET BLVD STE 71005 09 06SANTA BARBARA CA 93105-2164
LOS ANGELES CA 90069-34071HM818-222-7552 2BU818-986-462306 10 10
09 13 103CE818-321-1722 4CE1HM805-687-7286 2BU805-692-5415
1BU310/300-2905 2BU3FX 4CE
3FX 4CE
MR ROBERT D YOUNG, MRS DEBORAHMR RAYMOND C FREEMANMR RICHARD D LOBER
YOUNG CO-TTEES, YOUNG FAMILYMR STEVEN K RIDGEWAY CO-TTEESMRS LORAINE M LOBER CO-TTEES
TRUST UA DTD 12/05/89, LORDFREEMAN 1976 TRUSTLOBER FAMILY TRUST
ABBETT CONV MID CAPUA DTD 12/30/76UA DTD 04/23/93
1882 SHERIDAN AVE100 N HOPE AVE STE 20C510 SHARON RD
SAN DIEGO CA 92103-1636SANTA BARBARA CA 93110-2634ARCADIA CA 91007-7952
09 11 0905 12 0605 09 09
1HM619-291-7978 2BU619-474-82461HM805-687-1173 2BU1HM626-446-7694 2BU
3CE619-733-5504 4CE3FX 4CE3FX 4CE
MS OI LING CHAN TTEEMR TIMOTHY M COLEN &MR IRVING RABIN, MRS VARDA
OI LING CHAN LIVING TRUSTMS MARYALICE COLEN JT TENRABIN CO-TTEES, ARIEL HANOCH
UA DTD 09/21/01TCW LC GROWTHRABIN TRUST UA DTD 12/29/81,
11224 ARROWOOD ST225 EDGEHILL WAY(TCW)
ARCADIA CA 91006-5906SAN FRANCISCO CA 94127-1062298 SAN BRUNO AVE
05 07 0705 07 06SAN FRANCISCO CA 94103-5018
1HM626-444-7545 2CE626-266-01641HM415-661-4148 2BU415601170909 13 12
3FX 4CE3FX 4CE1HM415-789-8855 2BU415-522-5700
3FX415-522-5701 4CE
MR IRVING RABIN, MRS VARDAMR IRVING RABIN, MRS VARDAMR IRVING RABIN, MRS VARDA
RABIN CO-TTEES, DANIEL MENAHEMRABIN CO-TTEES, SHIRA RABINRABIN CO-TTEES, ORLEE RABIN
RABIN TRUST UA DTD 12/26/84,TRUST UA DTD 12/29/81, (TCW)TRUST UA DTD 12/21/83, (TCW)
(TCW)298 SAN BRUNO AVE298 SAN BRUNO AVE
298 SAN BRUNO AVESAN FRANCISCO CA 94103-5018SAN FRANCISCO CA 94103-5018
SAN FRANCISCO CA 94103-501809 13 1209 13 12
09 13 121HM415-789-8855 2BU1HM415-789-8855 2BU
1HM415-789-8855 2BU3FX 4CE3FX 4CE
3FX 4CE




<colgroup><col span="3"></colgroup><tbody>
</tbody>
there are a couple blank rows before the next set of names begin.
Is there a quick way for me to copy and paste (transpose) the first row after each blank row set?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You must log in or register to reply here.

Similar threads

M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
M
  • Question
One Conditional Format to Check Different Ranges Depending on the Position of the Root Cell
Replies
4
Views
663
Special-K99
S

Forum statistics

Threads
1,215,038
Messages
6,122,798
Members
449,095
Latest member
m_smith_solihull

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