Extracting Data

[bobzy20]

Hi,

Could you please help me with finding the best way to extract the data from the 'data to be extracted' column and dividing it up into three columns like below.

There will be multiple cells but they all follow the same format.

Data to be Extracted	Extract 1	Extract 2	Extract 3
22 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	22-Jan	ROYAL MAIL GROUP LTD CHESTERFIELD	£4.54

Thanks very much.

 

[etaf]

are you still using 2010 version of excel

will the dates for single days , like 1st 2nd be
01 jan ,.......
or just
1 jan ......
will the end of the post office ALWAYS be GBR
and then any amount in 10000's

=TRIM(LEFT(A1,6))
=TRIM(MID(A1,FIND("ROYAL",A1,1), FIND("GBR",A1,1)-4))
=RIGHT(A1,LEN(A1)-(FIND("GBR",A1,1)+3))*1

NOTE FIND() is case sensitive - so may be worth changing to search() if you have a mixture of upper and lowercase letters

would be worth adding a few more examples of the text if different

Book4
	A	B	C	D
1	22 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	22 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
2	23 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 6.24	23 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	6.24
3	24 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	24 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
4	25 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	25 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
5	26 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	26 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
6	27 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	27 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
7	28 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	28 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
8	29 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	29 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
9	30 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	30 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
10	31 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	31 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
11	1 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	1 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
12	2 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	2 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
13	3 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	3 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
14	4 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	4 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
15	5 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	5 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
16	6 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	6 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
17	7 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	7 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
18	8 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54	8 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4.54
19	9 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4000000.54	9 Jan	ROYAL MAIL GROUP LTD CHESTERFIELD GBR	4000000.54
Sheet2

Cell Formulas
Range	Formula
B1:B19	B1	=TRIM(LEFT(A1,6))
C1:C19	C1	=TRIM(MID(A1,FIND("ROYAL",A1,1), FIND("GBR",A1,1)-4))
D1:D19	D1	=RIGHT(A1,LEN(A1)-(FIND("GBR",A1,1)+3))*1

 

[bobzy20]

Hi,

Thanks, it works well.

The date format will always be the same, the main text will be a mixture of upper and lower case words and the price format will sometimes change, as per the below.

I don't actually need the word 'GBR' or 'DEU', so if this can be left out that would be great.

22 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.54

22 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 5.98

22 Jan TeamViewer GmbH Goeppingen DEU 228.96

23 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 32.71

24 Jan GOOGLE *YouTube 650-253-0000 GBR 1.99

 

[Joe4]

Here is one way:

Formula in cell B2:

=SUBSTITUTE(TRIM(LEFT(A2,6))," ","-")

Formula in cell C2:

=TRIM(MID(LEFT(A2,LEN(A2)-LEN(D2)-5),7,100))

Formula in cell D2:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

 

[bobzy20]

Thanks very much for your help, this is working.

 

[Joe4]

You are welcome.
Glad we were able to help!