Extracting Data from Cell string

[flwoOrlando]

I have data in my cell d2 that has lots and lots of data merged together. I am attempting to write a formula that finds the specific word, and then give me the code associated with it. the data I want is never in a set format/length.

The formula I am attempting to use is: =MID(D392,FIND("BR ",D392)+2,(FIND("G",D392)-(FIND("CANADA ",D392)+9)))

In this code, I am attempting to get the code that comes after CO+BR but stops before the next region.

I would then create another column, and call it Canada, and want the code/digits that come after Canada but before CO+BR.

The data looks like this when copied out of excel:

[TABLE="width: 1842"]
<tbody>[TR]
[TD="width: 1842"]HS Codes

Country/group Code





Argentina 3901105010

CANADA 3901109091

CO + BR 3901

Europe 39011010

General 39011050

SINGAPORE 39011050

SWITZERLAND 39011010

THAILAND 39011050

TURKEY 39011050

UKRAINE 39011010

USA 3901105010 [/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Something like this might work:

Book1
	A	B	C	D	E	F
1		CO + BR	CANADA	Europe	THAILAND	USA
2	HS Codes Country/group Code Argentina 3901105010 CANADA 3901109091 CO + BR 3901 Europe 39011010 General 39011050 SINGAPORE 39011050 SWITZERLAND 39011010 THAILAND 39011050 TURKEY 39011050 UKRAINE 39011010 USA 3901105010	3901	3901109091	39011010	39011050	3901105010
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(MID($A2,FIND(B$1,$A2)+LEN(B$1)+1,IFERROR(FIND(CHAR(10),$A2,FIND(B$1,$A2)),LEN($A2)+1)-FIND(B$1,$A2)-LEN(B$1)-1))

Copy B2 formula across columns as necessary

WBD

 

[flwoOrlando]

This is wonderful. Thank you!!!!!!!!!!!!!!!!

something like this might work:

	a	b	c	d	e	f
co + br	canada	europe	thailand	usa
hs codes country/group code argentina 3901105010 canada 3901109091 co + br 3901 europe 39011010 general 39011050 singapore 39011050 switzerland 39011010 thailand 39011050 turkey 39011050 ukraine 39011010 usa 3901105010	3901109091	3901105010

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[td="align: Center"]1[/td]
[td="align: Right"][/td]

[td="align: Center"]2[/td]

[td="align: Right"]3901[/td]

[td="align: Right"]39011010[/td]
[td="align: Right"]39011050[/td]

</tbody>

sheet1​

[table="width: 85%"]
<tbody>[tr]
[td]worksheet formulas[table="width: 100%"]
<thead>[tr="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]
[th="width: 10px"]cell[/th]
[th="align: Left"]formula[/th]
[/tr]
</thead><tbody>[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]b2[/th]
[td="align: Left"]=trim(mid($a2,find(b$1,$a2)+len(b$1)+1,iferror(find(char(10),$a2,find(b$1,$a2)),len($a2)+1)-find(b$1,$a2)-len(b$1)-1))[/td]
[/tr]
</tbody>[/table]
[/td]
[/tr]
</tbody>[/table]



copy b2 formula across columns as necessary

wbd