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.

 

[Fluff]

Delete any code you have already copied, along with any modules you may have created.
Then Insert a new module, copy the code from post#29 & paste it in the new module, save the workbook as macro enabled .xlsm file, if it isn't already & then try & run it.

 

[Myhobohemia]

I will try it! Thank you so much. ?

 

[Myhobohemia]

It worked! Thank you!! It was instant. Since that time, I have been trying to copy-paste a sample and ask a couple more questions, but now my XL2BB add-in disappeared. ?! Do I have to uninstall it when I downloaded it only yesterday? No, right? Then why won't it come up? I went to the file itself and tried to run it and nothing happened.

 

[Fluff]

Go to the folder that contains the XL2BB add-in & right click on the file & properties & check Unblock.
Then on the developer ribbon > "Excel add-ins" > check the box by XL2BB

 

[Myhobohemia]

Weirdly, 1) it was unblocked in "properties" and 2) it was checked in "Excel Add-ins."

 

[Fluff]

Do you have the MrExcel tab on the ribbon?

 

[Myhobohemia]

I did yesterday, but it disappeared. Both from yesterday's Excel, and the brand new Excel I just created to insert the module. And yet the add-in appears as active.

 

[Fluff]

What happens if you move it to this folder
C:\Users\your username\AppData\Roaming\Microsoft\AddIns

 

[Myhobohemia]

Well. This has really been a .... how should I put it... voyage of discovery. Folders aren't there, then they are. Then they aren't. There is no "AppData" folder under my user name. But there was under a Default User I didn't know was there. I found this only by using the search feature. Now it doesn't appear at all. You can't see it visually as a folder.

 

[Fluff]

Your settings are probably set to "Don't show hidden files, folders or drives"