Excel - VBA function to dissolve text string

[Grupper]

Hey guys,

Hope someone can help me with a VBA function I can not quite see how to solve. I have a long list of addresses, such as:

Myrdalsvegen 10, 12, 14, Myrdalshovden 22, 26A-F, Bergen
Moholt Allé 9, 11, 13, Fiolsvingen 3, 5, 7, Trondheim
...

I'm trying to create a macro that extracts the individual address like this:

Myrdalsvegen 10, Bergen
Myrdalsvegen 12, Bergen
Myrdalsvegen 14, Bergen
Myrdalshovden 22, Bergen
Myrdalshovden 26A-F, Bergen
...

I have tried using LEFT, RIGHT, FIND in a loop, but it wasn't very elegant and didn't get the job done. Does anyone have any nice solutions or ideas on how to solve this? Would really appreciate the help.

Thank you very much in advance.

[xladept]

Some selective splitting might work!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JLGWhiz]

the data is too erratic in structure to use either formulas or VBA with any degree of accuracy for the output. The data would need to be a bit more organized with unique characters to separate the city grouping. Then it might be feasible to use something like a Split function to to produce the desired output. Unforutunately, the computer cannot think and has to be directed as to what it looks for logically. However, some might give it a try.

[xladept]

If you'll post a sample, I'm willing to try it

[Grupper]

Hi again,

Really appreciate you trying. I don't think it is impossible but certainly challenging. There is some sort of structure. The city is always last, after the last comma. The other ones are addresses. Also - it working in 90 % of the cases would still save me a ton of time. I would scan through after and fix the ones that are not done/done correctly by the macro. Thanks for helping

[xladept]

Try this:

Please Login or Register  to view this content.

[Grupper]

Wow! That's amazing. Thank you so much

[xladept]

You're welcome and thanks for the rep!

[xladept]

Hi Grupper,

If the Queue would only be 10 or less times longer then this version will run rather quickly:

Please Login or Register  to view this content.