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

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

here my database

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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]