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

M

Myhobohemia

Board Regular
Joined
Mar 12, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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! :oops: 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.
 

Attachments

  • 20.03.12 Address data that needs to be transposed.JPG
    20.03.12 Address data that needs to be transposed.JPG
    54.2 KB · Views: 49
Sort by date Sort by votes
Ok, as a start, this will transpose the data into rows starting in B1
VBA Code: 
Sub myhobohemia()
   Dim StartRng As Range, Rng As Range
   Dim i As Long
   With ActiveSheet
      Set StartRng = Range("A1")
      For i = 1 To .Hyperlinks.Count
         Set Rng = .Range(StartRng, .Hyperlinks(i).Range)
         .Range("B" & i).Resize(, Rng.Count).Value = Application.Transpose(Rng)
         .Hyperlinks.Add .Cells(i, Rng.Count + 1), .Hyperlinks(i).Address, , , .Hyperlinks(i).TextToDisplay
         Set StartRng = .Hyperlinks(i).Range.Offset(1)
      Next i
   End With
End Sub
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
@Fluff: I'd like to if possible, but I'm willing to forego it.

@Hi Snakehips, my data does not include the headings... I will need to add them. Re specific columns like "Nuclear/PET CT"... that's a great idea. But I imagine it would complicate things more.

Aside from simply transposing the data, the other thing I most need is to get the data into the right columns. But I'm assuming this will have to be manual. Here's an example of what I mean.

Book3
ABCDEFGHIJKLM
1LIFESTYLE MGMNT OF BHAM10 Old Montgomery, Ste 100Birmingham, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021LIFESTYLE MGMNT OF BHAM
2Brookwood Internists513 Brookwood Blvd.Suite 50Birmingham, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020Brookwood Internists
3Cardiology, PC817 Princeton Avenue SWSuite 202Birmingham, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Feb 28, 2023Cardiology, PC
4Cardiology, PC801 Princeton Avenue SWSuite 106Birmingham, ALNuclear/PET: Myocardial Perfusion Imaging, Equilibrium Radionuclide AngiographyAccredited Through: Feb 28, 2023Cardiology, PC
5Simon Williamson Clinic Nuclear Medicine Lab832 Princeton Avenue, S.W.Birmingham, ALNuclear/PET: Myocardial Perfusion Imaging, General Nuclear MedicineAccredited Through: Sep 30, 2020Simon Williamson Clinic Nuclear Medicine Lab
6The Kirklin Clinic of UAB Hospital2000 6th Avenue South4th Floor Nuclear CardiologyBirmingham, ALNuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingAccredited Through: Aug 31, 2022The Kirklin Clinic of UAB Hospital
7Birmingham Heart Clinic, St. Vincents Birmingham Cardiology, LLC2217 Decatur HwyGardendale, ALEchocardiography: Adult TransthoracicAccredited Through: May 30, 2020Nuclear/PET: Myocardial Perfusion ImagingAccredited Through: Mar 31, 2020Birmingham Heart Clinic, St. Vincents Birmingham Cardiology, LLC
8Southview Medical Group PC833 St. Vincent’s Dr. POB 3, Suite 300Birmingham, ALEchocardiography: Adult TransthoracicAccredited Through: Apr 28, 2020Nuclear/PET: Myocardial Perfusion Imaging, Equilibrium Radionuclide AngiographyAccredited Through: Jan 31, 2021Southview Medical Group PC
9Seale Harris Clinic, PC805 St. Vincent’s DriveSuite 510Birmingham, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: May 31, 2020www.sealeharris.com
10Advanced Cardiovascular, LLC868 North Dean RoadSuite CAuburn, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020Advanced Cardiovascular, LLC
11Norwood Clinic339 Walker Chapel PlazaSuite 101Fultondale, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020Norwood Clinic
12Birmingham Heart Clinic100 Pilot Medical DriveSuite 300Birmingham, ALVascular: Peripheral Venous TestingAccredited Through: Feb 28, 2021Echocardiography: Adult TransthoracicAccredited Through: May 30, 2020CT: Vascular CTA, Coronary CTA, Coronary Calcium ScoringAccredited Through: May 31, 2020Nuclear/PET: Myocardial Perfusion Imaging, Positron Emission Tomography (PET)Accredited Through: Mar 31, 2020Birmingham Heart Clinic
13Southeast Cardiology - Enterprise409 N Main StreetEnterprise, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 28, 2020Southeast Cardiology - Enterprise
14Professional Medical Associates, PC101 East Brunson Street, Suite 200Enterprise, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021Professional Medical Associates, PC
15Alabama Cardiovascular Group, PC5890 Valley RoadSuite 200Birmingham, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Oct 31, 2022www.alcardio.com/
16Houston County Healthcare Authority101 Professional Ln, AEnterprise, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021www.samc.org/index.php/about-us-emc/primary-care-fac/home
17Premier Cardiology Consultants806 Glover Ave, Ste AEnterprise, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021Premier Cardiology Consultants
18Premier Cardiology Consultants320 White AveOzark, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021Premier Cardiology Consultants
19Pell City Internal and Family Medicine7067 Veterans Parkway Suite 130Pell City, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021Pell City Internal and Family Medicine
20Birmingham Heart Clinic, P.C., Northside Medical Associates74 Plaza DriveSuite 2BPell City, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Mar 31, 2020Birmingham Heart Clinic, P.C., Northside Medical Associates
21Premier Health Center, LLC2165 Hwy 78, Ste 100Dora, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021Premier Health Center, LLC
22Advanced Cardiovascular, LLC965 Highway 431Roanoke, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020Advanced Cardiovascular, LLC
23Advanced Cardiovascular, LLC209 Fob James DriveValley, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020Advanced Cardiovascular, LLC
24Pinnacle Cardiovascular Associates1701 48th StreetValley, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Feb 28, 2021Pinnacle Cardiovascular Associates
25John F. Simmons, MD, PC915 West Hospital DriveGeneva, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021John F. Simmons, MD, PC
26Geneva Medical Group701 West Maple AvenueGeneva, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021Geneva Medical Group
27Jacksons Point of Light Family Medicine1810 Stadium Dr., Suite 210Phenix City, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Apr 30, 2021Jacksons Point of Light Family Medicine
28Anniston Cardiology Associates, PA901 Leighton AvenueSuite 201Anniston, ALNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Jul 31, 2021Anniston Cardiology Associates, PA
Sheet1
 
Upvote 0
OMG IT WORKED!!!! On the first try, perfectly! I don't know how to thank you!!!! ? ? ?

Book4
ABCDEFGHIJK
1Abrazo Medical Group dba Biltmore CardiologyAbrazo Medical Group dba Biltmore Cardiology708 S Coeur D’AlenePayson, AZNuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingAccredited Through: Jun 30, 2020Abrazo Medical Group dba Biltmore Cardiology
2708 S Coeur D’AleneIMS Cardiology111 W. Cedar LanePayson, AZEchocardiography: Adult TransthoracicAccredited Through: May 31, 2022Nuclear/PET: Myocardial Perfusion ImagingAccredited Through: May 31, 2022IMS Cardiology
3Payson, AZAli A Askari PC1106 N Beeline HwyPayson, AZNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Jun 30, 2021Ali A Askari PC
4Nuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingCardiovascular Associates of Arizona, LLC5882 S. Hospital DrSuite #1Globe, AZNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Jun 29, 2020Cardiovascular Associates of Arizona, LLC
5Accredited Through: Jun 30, 2020Las Sendas Cardiology3514 North Power RoadSuite 107Mesa, AZNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Jun 30, 2020Las Sendas Cardiology
6Abrazo Medical Group dba Biltmore CardiologyTri-City Cardiology Consultants, PC6750 BaywoodSuite 301Mesa, AZNuclear/PET: Positron Emission Tomography (PET), Myocardial Perfusion Imaging, Equilibrium Radionuclide AngiographyAccredited Through: Oct 31, 2022Tri-City Cardiology Consultants, PC
7IMS CardiologyTri-City Cardiology Scottsdale8675 E Bell RoadSte 110Scottsdale, AZNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Oct 31, 2022Tri-City Cardiology Scottsdale
8111 W. Cedar LaneThe Cardiac Arrhythmia Institute10238 E. Hampton Ave.Ste. 501Mesa, AZNuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingAccredited Through: Mar 31, 2021www.caiaz.com
9Payson, AZCardiovascular Consultants, Ltd. - Scottsdale North Office10210 N. 92nd StreetSuite 301Scottsdale, AZNuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020Cardiovascular Consultants, Ltd. - Scottsdale North Office
10Echocardiography: Adult TransthoracicCardiovascular Associates of Mesa, PC6116 East Arbor AvenueSuite 112Mesa, AZNuclear/PET: Myocardial Perfusion ImagingAccredited Through: May 31, 2020cvam.com
11Accredited Through: May 31, 2022Cardiovascular Consultants, Ltd.3805 Bell RoadSuite 3100Phoenix, AZNuclear/PET: Positron Emission Tomography (PET), Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020Cardiovascular Consultants, Ltd.
12Nuclear/PET: Myocardial Perfusion ImagingIMS Cardiology3815 E. Bell RoadSuite 3300Phoenix, AZEchocardiography: Adult TransthoracicAccredited Through: May 31, 2022Nuclear/PET: Myocardial Perfusion ImagingAccredited Through: May 31, 2022IMS Cardiology
13Accredited Through: May 31, 2022San Tan Cardiovascular Center6859 East Rembrandt AveSte 117Mesa, AZNuclear/PET: Myocardial Perfusion Imaging, Equilibrium Radionuclide AngiographyAccredited Through: May 30, 2020San Tan Cardiovascular Center
14IMS CardiologyComprehensive Interventional Care Center4001 E. Baseline Rd.Suite #107Gilbert, AZNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Jun 29, 2020Comprehensive Interventional Care Center
15Ali A Askari PCCardiovascular Consultants, Ltd. - Scottsdale South Office3501 N. Scottsdale RoadSuite 348Scottsdale, AZNuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingAccredited Through: Oct 31, 2020cvcheart.com
161106 N Beeline HwyScottsdale Cardiovascular Center, P.C.3099 North Civic Center PlazaScottsdale, AZNuclear/PET: Myocardial Perfusion ImagingAccredited Through: Nov 30, 2022Scottsdale Cardiovascular Center, P.C.
17Payson, AZThe Cardiac Arrhythmia Institute1940 S. Country Club Dr.Ste.103Mesa, AZNuclear/PET: Myocardial Perfusion Imaging, Equilibrium Radionuclide AngiographyAccredited Through: Mar 31, 2021www.caiaz.com
18Nuclear/PET: Myocardial Perfusion ImagingArizona Heart Doctor, LLC2045 South Vineyard AvenueSuite 119Mesa, AZNuclear/PET: Myocardial Perfusion Imaging, Equilibrium Radionuclide AngiographyAccredited Through: Mar 31, 2020www.azheartdoctor.com/
19Accredited Through: Jun 30, 2021Tri-City Cardiology Consultants, PC1520 Dobson RoadSuite 209Mesa, AZNuclear/PET: Equilibrium Radionuclide Angiography, Myocardial Perfusion ImagingAccredited Through: Oct 31, 2022Tri-City Cardiology Consultants, PC
20Ali A Askari PCSouthwest Desert Cardiology, P.C.20414 N 27th Ave, Ste 300Phoenix, AZEchocardiography: Adult TransthoracicAccredited Through: Sep 30, 2021Nuclear/PET: Myocardial Perfusion ImagingAccredited Through: Mar 31, 2021www.southwestdesertcardiology.com
Sheet1
 
Upvote 0
Ok I just realized that to see the solution I should just look at the code. :rolleyes::biggrin: lol. Simple and beautiful!

I'm just wondering whether one more thing is possible. ?:oops: For the addresses with only a street address, and no suite number in a second line... somehow insert a cell so that all the data remains in the right column. So for example in the image above of the transposed data, for the first three practices (Abrazo Medical Group, IMS Cardiology, Ali Askari), the "D" column would be blank, and the City/State (Payson, AZ) would appear in Column E where it belongs.

?
 
Upvote 0
Would you have a maximum of 4 lines for the address?
 
Upvote 0
How about something like this. Select column D.

If it finds "," it will push the cells along, leaving a blank space under suite.

VBA Code: 
Sub StateID()
Dim c As Range

For Each c In Selection
If InStr(c.Value, ",") Then c.Insert Shift:=xlToRight

Next c

End Sub
 
Upvote 0
@G12, PERFECT!

G12 and Fluff, between the pair of you, you've saved my life! lol. Thank you so much!!!! (y) ? :biggrin: :love:?????????☀!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Using INDEX...SMALL...IF...ROW and COUTNIF in order to match multiple peoples names to single email for mail marge
Replies
6
Views
875
AhoyNC
A
S
  • Question
Need help transposing groups of data within a column that have spaces between them
Replies
6
Views
368
shukero
S
S
  • Question
Column Header Name in Row 1 to Column Number
Replies
1
Views
387
sspatriots
S
O
  • Question
How can I convert a list of addresses, first and last names so that one unique address appears per row, while the names populate the columns behind?
Replies
3
Views
793
Akuini
Akuini
D
  • Question
Return a list of names (Column A) who have a specific text in any column of the same row (D:AH)
Replies
3
Views
602
Fluff
Fluff

Forum statistics

Threads
1,216,746
Messages
6,132,476
Members
449,729
Latest member
davelevnt

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