Extract data from stings and organise Help needed.

[Martynrbell]

Hi All,

I require some extracting and correlating data.


In my raw data i have a PA1 followed by a PA6
[TABLE="width: 245"]
<tbody>[TR]
[TD]
PA1*7*-1*Item one
PA6*81001234*Item one

I require the "7" and the "81001234" to be returned in specific places in a table so i can then do a look up against them.

One thing to note is the numbers are always between ** but are may not always by the same length in character.

an example of the raw data.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA1*7*-1*Item one[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA6*81001234*Item one[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA1*8*-1*Item two[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA6*81005678*Item two[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA1*9*-1*Item three[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA6*81009101*Item three[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA1*10*-1*Item four[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA6*81001112*Item four[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA1*11*-1*Item five[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 245"]PA6*81001213*Item five[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]PA1*12*-1*Item six[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65"]PA6*81001314*Item six[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65"]PA1*13*-1*Item seven[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65"]PA6*81001516*Item seven[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The returned result


[TABLE="width: 250"]
<tbody>[TR]
[TD]7[/TD]
[TD]81001234[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]81005678[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]81009101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]81001112[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]81001213[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]81001314[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]81001516[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Any and all help would be appreciated.

Thanks

Martyn[/TD]
[/TR]
</tbody>[/TABLE]

 

[Martynrbell]

Data without the boxes[TABLE="width: 245"]
<colgroup><col></colgroup><tbody>[TR]
[TD]

PA1*7*-1*Item one[/TD]
[/TR]
[TR]
[TD]PA6*81001234*Item one[/TD]
[/TR]
[TR]
[TD]PA1*8*-1*Item two[/TD]
[/TR]
[TR]
[TD]PA6*81005678*Item two[/TD]
[/TR]
[TR]
[TD]PA1*9*-1*Item three[/TD]
[/TR]
[TR]
[TD]PA6*81009101*Item three[/TD]
[/TR]
[TR]
[TD]PA1*10*-1*Item four[/TD]
[/TR]
[TR]
[TD]PA6*81001112*Item four[/TD]
[/TR]
[TR]
[TD]PA1*11*-1*Item five[/TD]
[/TR]
[TR]
[TD]PA6*81001213*Item five[/TD]
[/TR]
[TR]
[TD]PA1*12*-1*Item six[/TD]
[/TR]
[TR]
[TD]PA6*81001314*Item six[/TD]
[/TR]
[TR]
[TD]PA1*13*-1*Item seven[/TD]
[/TR]
[TR]
[TD]PA6*81001516*Item seven

Returned result

7 81001234
8 81005678
9 81009101
10 81001112
11 81001213
12 81001314
13 81001516



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

 

[jtakw]

Hi,

Would something like this help:

Book1
	A	B
1	PA1*7*-1*Item one	7 81001234
2	PA6*81001234*Item one
3	PA1*8*-1*Item two	8 81005678
4	PA6*81005678*Item two
5	PA1*9*-1*Item three	9 81009101
6	PA6*81009101*Item three
7	PA1*10*-1*Item four	10 81001112
8	PA6*81001112*Item four
9	PA1*11*-1*Item five	11 81001213
10	PA6*81001213*Item five
11	PA1*12*-1*Item six	12 81001314
12	PA6*81001314*Item six
13	PA1*13*-1*Item seven	13 81001516
14	PA6*81001516*Item seven
Sheet80

Cell Formulas
Range	Formula
B1	=IF(ISODD(ROW(A1)),TRIM(MID(SUBSTITUTE(A1,"*",REPT(" ",100)),100,100))&" "&TRIM(MID(SUBSTITUTE(A2,"*",REPT(" ",100)),100,100)),"")

Formula copied down.