Address Book Sort into different Columns

[tek9step]

Hi

Need help to sort out address book details into seperate columns. I have attached an example the raw data (which always appear in same format with same gap) shown in yellow (COLUMN A) and on the right how it should be presented. The address details needs to be split (not sure how to do) which is the 1st & 2nd line can be presented with Company Name appearing in Address 1 and the address details before the 1st coma appearing in column Address 2 and the following after the 1st coma appearing in Address 3 The last comma after which the post code appears should be shown in G column.

Either a formula or macro would do or any help to show as to how to do this. Please ask if any questions.

Thanks

[TMS]

Something to be going on with ...

Formulae in Row 13:

Copy down, and up if you wish.

At the moment, this doesn't split the address or the telephone/fax numbers.

You could do this with Text to Columns, delimited by comma (,) or you can improve the formulae to split the elements out. I don't have time to do that right now ... sorry.

Regards

[MarvinP]

Hi tek9step,

Find the attached with a set of string formulas on the first line of each of your addresses. Copy this formula down your page, copy and paste values only on another part of your worksheet and sort to get rid of blank rows. Hope this helps.

[tek9step]

Thanks Tm Shucks & Marvin P for your kind help

Marvin if i copy down the formula there is a slight problem on the postcode i have highlighted in yellow along with other info it is capturing.

[tek9step]

no worry i can live that i will do it manually. thanks again

[stanleydgromjr]

tek9step,

Detach/open workbook tek9step - EF754625 - SDG15.xlsm and run the ReorgData macro.

[TMS]

Sorry, it's taken me a little while to finish tinkering with your sample workbook.

I have attached an updated version for your review.

You should find that you can copy the formulae in row 2 down through the worksheet and you will get only one row of transcribed data for each block.

Copy and Paste Special values to remove the formulae with "blank" content.

Regards