Need to convert/transpose single column of addresses to separate columns (name, city, state, etc.), but not all addresses have the same number of rows

[Myhobohemia]

HI there. As I said in the subject line, I need to convert a single column of addresses and transpose them into their separate fields. This would be fairly easy, except that the number of rows is irregular, ranging from 6-13 rows. Most are about 7 or 8. I can't figure out how to automate this. Right now, the individual addresses in the column are not separated by anything. The last row of each address is a hyperlink, for what it's worth (and hyperlinks are searchable using the search feature, I learned yesterday, but to no avail). I have never done a macro or used VBA in my life! I can, however, follow clear instructions. ? I have dozens of separate documents, some with hundreds of addresses, some with only a handful. The worstcase scenario would be to have to transpose each address manually. But I'd like to avoid it, particularly since it looks like there will still be plenty of manual work later, getting everything into the right column. Any suggestions?! Thanks in advance. Attaching an image of the data.

 

[Myhobohemia]

Bingo. I just moved it. Now what? I closed the Excel file, and reopened it. Still no MrExcel tab, and nothing else appears to have changed.

 

[Fluff]

You may need to unblock it again & then in Xl deselect it in the add-in tab on the developer ribbon & then reselect it again

 

[Myhobohemia]

Ok, so it was still unblocked (when I select the file, right click "Properties"). So I deselected it in Excel Add-ins and re-selected, and... TA-DA! Mr. Excel appeared! Thank you for your patience! ?

So here's a sample of the data after I ran the code you gave me. All this was with one click!

GEORGIA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	City of Hope Heart and Vascular	City of Hope Heart and Vascular	207 Fairview Park Drive	Suite A	Dublin, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jun 29, 2020										City of Hope Heart and Vascular
2	207 Fairview Park Drive	Meyer Cardiology	112 Corporate Square		Dublin, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jul 31, 2022										Meyer Cardiology
3	Suite A	UND-Cardiopulmonary Associates	1031 Keth Drive		Perry, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jun 29, 2020										UND-Cardiopulmonary Associates
4	Dublin, GA	NT Cardiovascular Center	1117 Morningside Drive		Perry, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jun 29, 2020										NT Cardiovascular Center
5	Nuclear/PET: Myocardial Perfusion Imaging	Advanced Medical Associates	1282 South Houston Lake Road		Warner Robins, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Apr 30, 2021										www.advamed.org/
6	Accredited Through: Jun 29, 2020	Madalyn N. Davidoff, MD, LLC	1570 Watson Boulevard		Warner Robins, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jun 30, 2020										Madalyn N. Davidoff, MD, LLC
7	City of Hope Heart and Vascular	HOUSTON PRIMARY CARE	1719 RUSSELL PARKWAY BLDG 700		WARNER ROBINS, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Apr 30, 2021										HOUSTON PRIMARY CARE
8	Meyer Cardiology	Meyer Cardiology	312 Margie Drive		Warner Robins, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jul 31, 2022										Meyer Cardiology
9	112 Corporate Square	Prime Medical Associates, Inc.	305 Osigian Blvd		Warner Robins, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Apr 30, 2021										Prime Medical Associates, Inc.
10	Dublin, GA	Tifton Cardiovascular, LLC	1499 Kennedy Rd	Suite C	Tifton, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Oct 31, 2020										Tifton Cardiovascular, LLC
11	Nuclear/PET: Myocardial Perfusion Imaging	Macon Electrophysiology Associates	639 Hemlock St, Ste 100		Macon, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Apr 30, 2021										www.gacri.com
12	Accredited Through: Jul 31, 2022	Georgia Heart Physicians, LLC (Macon)	654 First Street		Macon, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: May 30, 2020										ghphysicians.com
13	Meyer Cardiology	Meyer Cardiology, P.C	360 Hospital Drive	Building D, Suite 200	Macon, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jul 31, 2022										Meyer Cardiology, P.C
14	UND-Cardiopulmonary Associates	Central Georgia Heart Center	1062 Forsyth Street	Suite 1B	Macon, GA	Nuclear/PET: Positron Emission Tomography (PET), Myocardial Perfusion Imaging	Accredited Through: Jan 31, 2021										Central Georgia Heart Center
15	1031 Keth Drive	Georgia Heart Physicians,LLC ( Fort Valley)	105 Avera Drive		Fort Valley, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: May 30, 2020										www.gaheartphysicians.com
16	Perry, GA	Heart of Georgia Cardiology	2064 Vineville Avenue		Macon, GA	Nuclear/PET: Myocardial Perfusion Imaging, Equilibrium Radionuclide Angiography	Accredited Through: May 31, 2021										Heart of Georgia Cardiology
17	Nuclear/PET: Myocardial Perfusion Imaging	Southeastern Cardiovascular Associates	1076 Bermuda Run Road		Statesboro, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jan 31, 2021										Southeastern Cardiovascular Associates
18	Accredited Through: Jun 29, 2020	Cardiovascular Associates of Eastern Georgia	1497 Fair Road	Suite 305	Statesboro, GA	Nuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion Imaging	Accredited Through: Aug 31, 2020										Cardiovascular Associates of Eastern Georgia
19	UND-Cardiopulmonary Associates	Kendall Griffith, MD, LLC	1497 Fair Road	Suite 311	Statesboro, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jan 31, 2021										Kendall Griffith, MD, LLC
20	NT Cardiovascular Center	Statesboro Cardiology, PC	5 Grady Johnson Road		Statesboro, GA	Vascular: Peripheral Venous Testing	Accredited Through: May 31, 2021	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Feb 28, 2021								Statesboro Cardiology, PC
21	1117 Morningside Drive	St. Vincent’s Ambulatory Care, Inc.	615 Pendleton Street	Suite B	Waycross, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: May 31, 2021										St. Vincent’s Ambulatory Care, Inc.
22	Perry, GA	Colquitt Regional Medical Center	3131 South Main Street		Moultrie, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jan 31, 2022										Colquitt Regional Medical Center
23	Nuclear/PET: Myocardial Perfusion Imaging	Atlanta Heart Associates Jackson Office	1502 West 3rd Street, Suite D		Jackson, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Sep 30, 2021										Atlanta Heart Associates Jackson Office
24	Accredited Through: Jun 29, 2020	Saint Joseph’s Hospital	11700 Mercy Boulevard	Plaza D Suite 6	Savannah, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Dec 31, 2022										Saint Joseph’s Hospital
25	NT Cardiovascular Center	University Cardiology Associates	4350 Towne Center Drive	MOB 4 Suite 3101	Evans, GA	Nuclear/PET: Positron Emission Tomography (PET)	Accredited Through: Dec 31, 2020										University Cardiology Associates
26	Advanced Medical Associates	University Hospital Cardiac Imaging of Evans	4350 Towne Center Drive	Suite 3100	Evans, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Dec 31, 2020										University Hospital Cardiac Imaging of Evans
27	1282 South Houston Lake Road	Heart and Vascular Associates of Augusta	820 St. Sebastian Way	Suite 2A	Augusta, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Mar 31, 2021										Heart and Vascular Associates of Augusta
28	Warner Robins, GA	University Hospital Cardiac Imaging of Augusta	818 St. Sebastian Way	Suite 305	Augusta, GA	Nuclear/PET: Positron Emission Tomography (PET), Myocardial Perfusion Imaging	Accredited Through: Dec 31, 2020										University Hospital Cardiac Imaging of Augusta
29	Nuclear/PET: Myocardial Perfusion Imaging	University Hospital Cardiovascular Imaging	1350 Walton Way	Suite 5300	Augusta, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Dec 31, 2020										University Hospital Cardiovascular Imaging
30	Accredited Through: Apr 30, 2021	University Hospital CVP Lab	1350 Walton Way	CVP Diagnostics	Augusta, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Dec 31, 2020										University Hospital CVP Lab
31	www.advamed.org/	Memorial Heart Care - Abercorn	6301 Abercorn Street		Savannah, GA	Nuclear/PET: Myocardial Perfusion Imaging	Accredited Through: Jun 30, 2020										memorialhealth.com/
Sheet1

 

[Myhobohemia]

First, to wrap up where we were before. The mistake I was making was trying to create a macro from "Macro." I did not realize I had to go to "Visual Basic / Insert Module." Instead, I was going to "Macros" and writing a new name in the field, which would make the greyed-out "create" button suddenly activate. Still, this was a mess. At one point I even created fake macros (making useless movements like widening columns) so that I could save, "edit," and replace the code. But it was still a mess. [I'm writing this out in case another total beginner ever ends up on this thread].

As it is now, the macro needs only three teensy things to be a perfect, one-click, nothing manual solution. I'm mentioning them only because, well, we've come this far! ? But even if I have to do them manually, it will take only a couple of minutes at most.

1) Move the state initials ("GA") into a separate column.
2) Remove "Accredited Through:" wherever it appears (I will make that a column heading instead).
3) Remove columns J,K,L,M,N,O,P which appear to be there for no reason.

If you can be bothered to do this, fantastic, but please believe me when I say: I'm already eternally grateful. !

 

[Fluff]

Would you be willing to upload the workbook to a share site such as OneDrive.
If so mark it for sharing & post the link you get to the thread.
With the 5 sets of data I get the links all lined up in col H

 

[Myhobohemia]

I sure would! Let me know if this works. There should be four states in it. GEORGIA.xlsm

 

[Fluff]

Many thanks for that, the problem is that in A648 on the Georgia shaeet you do not have a hyperlink, so two sets of data are being put on one line

 

[Myhobohemia]

I see! wow. Yes, there was a typo in it: "..com".... I fixed it. ?

 

[Fluff]

How about

Sub myhobohemia()
   Dim StartRng As Range, Rng As Range, Cl As Range
   Dim i As Long, Mx As Long
   
   Application.ScreenUpdating = False
   Set StartRng = Range("A1")
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If Cl.Hyperlinks.Count = 1 Then
         i = i + 1
         Set Rng = Range(StartRng, Cl)
         Range("B" & i).Resize(, Rng.Count).Value = Application.Transpose(Rng)
         Cl.Copy Cells(i, Rng.Count + 1)
         Set StartRng = Cl.Offset(1)
      End If
   Next Cl
   For Each Rng In Range("E1", Range("E" & Rows.Count).End(xlUp))
      If InStr(1, Rng, ":") > 0 Then Rng.Offset(, -1).Insert xlToRight
   Next Rng
   Range("F:F").Insert
   With Range("E:E")
      .Replace ", ", ",", xlPart, , , , False, False
      .TextToColumns Range("E1"), xlDelimited, xlDoubleQuote, , False, _
         False, True, False, False
   End With
   Mx = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
   With Range("G1", Cells(1, Mx - 1)).EntireColumn
      Do Until .Hyperlinks.Count = 0
         .Hyperlinks(1).Range.Resize(, Mx - .Hyperlinks(1).Range.Column).Insert xlToRight
      Loop
   End With
   With Range("H1", Cells(1, Mx - 1)).EntireColumn
      .Replace "Accredited Through: ", "", xlPart, , False, , False, False
   End With
End Sub

 

[Myhobohemia]

Hello! Sorry for delay in responding. It's great! Worked like a charm. Thank you!

I still have to some minor touch-ups to those "outlier" practices which have multiple accreditations when all the others have no more than two, and some minimal formatting, but I expected this. The lion's share is done!

This is what it will all look like when duly formatted, with the columns in the right places. Note that I inserted a blank "Zip Code" column.

Order No.

Practice Name

Street Address

Address line 2

City

State

Zip Code

Web link

Accred1

Accred1 Date

Accred2

Accred2 Date

Accred3

Accred3 Date

Accred4

Accred4 Date

1

Selma Family Care, PC

203 Vaughan Memorial Drive

Selma

AL

Selma Family Care, PC

Nuclear/PET: Myocardial Perfusion Imaging

Apr 30, 2021

2

Charlton Trinidad, M.D.

1739 East Main Street

Suite Ab

Prattville

AL

Charlton Trinidad, M.D.

Nuclear/PET: Myocardial Perfusion Imaging

Apr 30, 2021

3

MEDICAL CARE ASSOCIATES PRATTVILLE

102 MEDICAL CENTER DR SUITE A

Prattville

AL

MEDICAL CARE ASSOCIATES PRATTVILLE

Nuclear/PET: Myocardial Perfusion Imaging

Apr 30, 2021

4

Primary Care Internists

1722 Pine Street

Montgomery

AL

Primary Care Internists

Nuclear/PET: Myocardial Perfusion Imaging

Apr 30, 2021

5

John K. L. Porter, D.O., PC

1758 Park Pl Suite 100

Montgomery

AL

John K. L. Porter, D.O., PC

Nuclear/PET: Myocardial Perfusion Imaging

Apr 30, 2021

6

William O. Sargeant, PC

1758 Park Pl Suite 100

Montgomery

AL

William O. Sargeant, PC

Nuclear/PET: Myocardial Perfusion Imaging

Apr 30, 2021

7

Mulberry Medical Associates

1301 Mulberry Street

Montgomery

AL

Mulberry Medical Associates

Nuclear/PET: Myocardial Perfusion Imaging

Apr 30, 2021

My question is this. Are you familiar with an easy way to get zip codes to autopopulate? Google Sheets offers this apparently. But I would need to copy everything into Google and follow these instructions.

Before I do this, I wanted to check whether anyone knew an easier way. Otherwise, I will just use this Google Sheets method.