Sorting

[luskentyre]

Hi all,

I am looking to do a mail shot from a publication but need some assistance in sorting the data from a pdf.

The data comes in the following format:

OK1234567 R The food company Ltd
Director(s) Mr Joe Blogs, Mrs Joe Blogs
Unt A, Some Road, Some town, Some county, Some post code
Operating centre: Another town, another county, another post code
Authorisation: 1 vehicle(s)

OK1234567 R The food company Ltd
Director(s) Mr Joe Blogs, Mrs Joe Blogs
Unt A, Some Road, Some town, Some county, Some post code
Operating centre: Another town, another county, another post code
Authorisation: 1 vehicle(s)

OK1234567 R The food company Ltd
Director(s) Mr Joe Blogs, Mrs Joe Blogs
Unt A, Some Road, Some town, Some county, Some post code
Operating centre: Another town, another county, another post code
Authorisation: 1 vehicle(s)

I have copied the above data into excel (2016) and used the text to columns format but this does not sort the data as I want it.

I have then copied each chunk of data and then used the paste special transpose function which gets me to more where i need to be but is there a quick way to get the data into the following format:

OK1234567 R, The Food Company Ltd, Director(s) Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code, Operating centre: Another town, another county, another post code

 

[Logit]

.
Do you want the resulting transposing to be in a single cell or can it be in different cells on the same row ?

In other words, do you want : "OK1234567 R, The Food Company Ltd, Director(s) Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code, Operating centre: Another town, another county, another post code"
to all be displayed in a single cell .... or can it be displayed in separate cells on the same row like this :

	A​	B​	C​	D​	E​	F​
1​		OK1234567 R The food company Ltd	Director(s) Mr Joe Blogs, Mrs Joe Blogs	Unt A, Some Road, Some town, Some county, Some post code	Operating centre: Another town, another county, another post code	Authorisation: 1 vehicle(s)

 

[luskentyre]

.
Do you want the resulting transposing to be in a single cell or can it be in different cells on the same row ?

In other words, do you want : "OK1234567 R, The Food Company Ltd, Director(s) Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code, Operating centre: Another town, another county, another post code"
to all be displayed in a single cell .... or can it be displayed in separate cells on the same row like this :

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​		OK1234567 R The food company Ltd	Director(s) Mr Joe Blogs, Mrs Joe Blogs	Unt A, Some Road, Some town, Some county, Some post code	Operating centre: Another town, another county, another post code	Authorisation: 1 vehicle(s)

<tbody>
</tbody>

It would be handy to get the data to be used as mail merge and therefore take the format:

OK1234567, R, The food company Ltd, Director(s), Mr Joe Blogs, Mrs Joe Blogs, Unt A, Some Road, Some town, Some county, Some post code etc etc etc

Is this possible?