Extracting Data

bobzy20

New Member
Joined
Feb 5, 2018
Messages
47
Office Version
  1. 2010
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 ExtractedExtract 1Extract 2Extract 3
22 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5422-JanROYAL MAIL GROUP LTD CHESTERFIELD£4.54

Thanks very much.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
ABCD
122 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5422 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
223 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 6.2423 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR6.24
324 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5424 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
425 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5425 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
526 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5426 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
627 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5427 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
728 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5428 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
829 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5429 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
930 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5430 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
1031 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.5431 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
111 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.541 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
122 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.542 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
133 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.543 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
144 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.544 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
155 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.545 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
166 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.546 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
177 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.547 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
188 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4.548 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4.54
199 Jan ROYAL MAIL GROUP LTD CHESTERFIELD GBR 4000000.549 JanROYAL MAIL GROUP LTD CHESTERFIELD GBR4000000.54
Sheet2
Cell Formulas
RangeFormula
B1:B19B1=TRIM(LEFT(A1,6))
C1:C19C1=TRIM(MID(A1,FIND("ROYAL",A1,1), FIND("GBR",A1,1)-4))
D1:D19D1=RIGHT(A1,LEN(A1)-(FIND("GBR",A1,1)+3))*1
 
Last edited:
Upvote 0
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
 
Upvote 0
Here is one way:

1713964949190.png


Formula in cell B2:
Excel Formula:
=SUBSTITUTE(TRIM(LEFT(A2,6))," ","-")

Formula in cell C2:
Excel Formula:
=TRIM(MID(LEFT(A2,LEN(A2)-LEN(D2)-5),7,100))

Formula in cell D2:
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
 
Upvote 0
Solution
You are welcome.
Glad we were able to help!
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top