EXTRACTion problem???

[excelindianfanclub]

hi experts,

i have a database in range D4:D1000. But im just posting sample range where i had a problem.i need to extract the mixed values from range D4 to D19 as S.no,Description,Q.ty and Part No. I used Text to Columns but it was not giving exact extraction what i needed.so, i made Formulae as follows


[TABLE="width: 890"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 593"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]G4(extracting serial no.)[/TD]
[TD] =LEFT(D4,SEARCH(" ",D4)-1)[/TD]
[/TR]
[TR]
[TD]H4(extracting material description)[/TD]
[TD] =MID(D4,SEARCH(" ",D4)+1,LEN(D4)-13)[/TD]
[/TR]
[TR]
[TD]I4(Extracting quantity installed)[/TD]
[TD] =MID(D4,LEN(G4)+LEN(H4)+3,1)[/TD]
[/TR]
[TR]
[TD]J4(Part No.)[/TD]
[TD] =RIGHT(D4,8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


here my database

[TABLE="width: 899"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S.No[/TD]
[TD]Description[/TD]
[TD]Q.ty[/TD]
[TD]Part No.[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1 SHROUD 1 26258368[/TD]
[TD]1[/TD]
[TD]SHROUD[/TD]
[TD]1[/TD]
[TD]26258368[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2 CUP WASHER 1 26254222[/TD]
[TD]2[/TD]
[TD]CUP WASHER[/TD]
[TD]1[/TD]
[TD]26254222[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3 PLUNGER SPRING 1 54643779[/TD]
[TD]3[/TD]
[TD]PLUNGER SPRING[/TD]
[TD]1[/TD]
[TD]54643779[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4 PLUNGER ASSY. 1 26257121[/TD]
[TD]4[/TD]
[TD]PLUNGER ASSY.[/TD]
[TD]1[/TD]
[TD]26257121[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5 SOLENOID SWITCH 1 26240836[/TD]
[TD]5[/TD]
[TD]SOLENOID SWITCH[/TD]
[TD]1[/TD]
[TD]26240836[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]6 SPINDLE ASSY. 1 26240796[/TD]
[TD]6[/TD]
[TD]SPINDLE ASSY.[/TD]
[TD]1[/TD]
[TD]26240796[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]7 TERMINAL BASE ASSY. 1 26240837[/TD]
[TD]7[/TD]
[TD]TERMINAL BASE ASSY.[/TD]
[TD]1[/TD]
[TD]26240837[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8 FIELD COIL ASSY. 1 26258367[/TD]
[TD]8[/TD]
[TD]FIELD COIL ASSY.[/TD]
[TD]1[/TD]
[TD]26258367[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]9 ARMATURE ASSY. 1 26240566[/TD]
[TD]9[/TD]
[TD]ARMATURE ASSY.[/TD]
[TD]1[/TD]
[TD]26240566[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10 INTER BRACKET ASSY. 1 26252463[/TD]
[TD]10[/TD]
[TD]INTER BRACKET ASSY. [/TD]
[TD] [/TD]
[TD]26252463[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]11 ENGAGING LEVER ASSY. 1 26252534[/TD]
[TD]11[/TD]
[TD]ENGAGING LEVER ASSY. [/TD]
[TD] [/TD]
[TD]26252534[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]12 DRIVE ASSY. 1 26253936[/TD]
[TD]12[/TD]
[TD]DRIVE ASSY. [/TD]
[TD] [/TD]
[TD]26253936[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]13 FIXING BRACKET ASSY. 1 26253930[/TD]
[TD]13[/TD]
[TD]FIXING BRACKET ASSY. [/TD]
[TD] [/TD]
[TD]26253930[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]14 BRUSH ASSY. 1 26256401[/TD]
[TD]14[/TD]
[TD]BRUSH ASSY. [/TD]
[TD] [/TD]
[TD]26256401[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]15 C.E. BRACKET ASSY. 1 26252868[/TD]
[TD]15[/TD]
[TD]C.E. BRACKET ASSY. [/TD]
[TD] [/TD]
[TD]26252868[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]16 ARMATURE KIT 1 26241434[/TD]
[TD]16[/TD]
[TD]ARMATURE KIT [/TD]
[TD] [/TD]
[TD]26241434[/TD]
[/TR]
</tbody>[/TABLE]

the part number is always eight digits.i able to extract everything but the problem arised when i try to extract the quantity from D13 till the end of my database. The formula was unable to extract the value from mixed value.please help me to extract and also if any other possible solution is available.please share it. thanks in advance.

 

[Momentman]

The following looks cumbersome but should work if I understand your data very well

G4 = LEFT(A1,FIND(" ",D4)-1)
H4 = MID(D4,FIND(" ",D4)+1,FIND("*",SUBSTITUTE(D4," ","*",LEN(D4)-LEN(SUBSTITUTE(D4," ",""))-1))-FIND(" ",D4)-1)
I4 = MID(D4,LEN(D4)-9,1)
J4 = RIGHT(D4,8)

 

[norulen]

This formula should work for you. However you might require few small changes when the qty is more than 1 digit. I suggest try these (worked out on the basis of Momentman formulas:

G1=LEFT(D1,FIND(" ",D1)-1)
H1=MID(D1,FIND(" ",D1)+1,FIND("*",SUBSTITUTE(D1," ","*",LEN(D1)-LEN(SUBSTITUTE(D1," ",""))-1))-FIND(" ",D1)-1)
I1=RIGHT(LEFT(D1,LEN(D1)-9),(LEN(LEFT(D1,LEN(D1)-9))-LEN(G1)-LEN(H1)-2))
J1=RIGHT(D1,8)

The following looks cumbersome but should work if I understand your data very well

G4 = LEFT(A1,FIND(" ",D4)-1)
H4 = MID(D4,FIND(" ",D4)+1,FIND("*",SUBSTITUTE(D4," ","*",LEN(D4)-LEN(SUBSTITUTE(D4," ",""))-1))-FIND(" ",D4)-1)
I4 = MID(D4,LEN(D4)-9,1)
J4 = RIGHT(D4,8)

 

[Rick Rothstein]

How about these formulas...

G1: =LEFT(D1,FIND(" ",D1))

H1: =SUBSTITUTE(MID(D1,FIND(" ",D1)+1,999)," "&I1&" "&J1,"")

I1: ==TRIM(LEFT(RIGHT(SUBSTITUTE(" "&D1," ",REPT(" ",999)),1998),999))

J1: =RIGHT(D1,8)

 

[excelindianfanclub]

Thanks to Norulen and Rick.. its worked very much. But Rick please explain your approach.im unable to understand.please share your knowledge if you wish.

 

[excelindianfanclub]

thanks to all very much.. i owe you all..

 

[norulen]