Auto Splitting of Data

[Ruban S]

Dear,

I have data like this

SHORT	LONG	NOUN	MODIFIER	ATT N1	ATT V1	ATT N2	ATT V2	ATT N3	ATT V3
CHEMICAL,FAST-DRY SELF VULCANIZING CEMNT	CHEMICAL, TYPE:FAST-DRY SELF VULCANIZING CEMENT, PACK SIZE:236.6ML	CHEMICAL	--	TYPE		PACK SIZE
CHEMICAL,FLUSH,LOCO,236.6ML	CHEMICAL,FLUSH, BRAND:LOCO, PACK SIZE:236.6ML	CHEMICAL	FLUSH	MANUFACTURER		BRAND		PACK SIZE

I Need it like this

SHORT	LONG	NOUN	MODIFIER	ATT N1	ATT V1	ATT N2	ATT V2	ATT N3	ATT V3
CHEMICAL,FAST-DRY SELF VULCANIZING CEMNT	CHEMICAL, TYPE:FAST-DRY SELF VULCANIZING CEMENT, PACK SIZE:236.6ML	CHEMICAL	--	TYPE	FAST-DRY SELF VULCANIZING CEMENT	PACK SIZE	236.6ML
CHEMICAL,FLUSH,LOCO,236.6ML	CHEMICAL,FLUSH, BRAND:LOCO, PACK SIZE:236.6ML	CHEMICAL	FLUSH	MANUFACTURER		BRAND	LOCO	PACK SIZE	236.6ML

Likewise, I have many records, Please help me with some solution.
Data has to be Autofill based on LONG

 

[kweaver]

What's the "key" to decide if you want ATT V1 versus ATT V2, etc.? Is it "TYPE" and "BRAND" ?

 

[kweaver]

Not fully tested with other options, but consider this an approach:

test..xlsx.xlsm
	A	B	C	D	E	F	G	H	I	J
1	SHORT	LONG	NOUN	MODIFIER	ATT N1	ATT V1	ATT N2	ATT V2	ATT N3	ATT V3
2	CHEMICAL,FAST-DRY SELF VULCANIZING CEMNT	CHEMICAL, TYPE:FAST-DRY SELF VULCANIZING CEMENT, PACK SIZE:236.6ML	CHEMICAL	--	TYPE	FAST-DRY SELF VULCANIZING CEMENT	PACK SIZE	236.6ML
3	CHEMICAL,FLUSH,LOCO,236.6ML	CHEMICAL,FLUSH, BRAND:LOCO, PACK SIZE:236.6ML	CHEMICAL	FLUSH	MANUFACTURER		BRAND	LOCO	PACK SIZE	236.6ML
Sheet2

Cell Formulas
Range	Formula
F2:F3	F2	=IF(ISERROR(FIND("TYPE:",B2)),"",MID(B2,5+FIND("TYPE:",B2),(FIND("PACK",B2)-3)-(4+FIND("TYPE:",B2))))
H2:H3	H2	=IF(ISERROR(FIND("TYPE:",B2)),MID(B2,6+FIND("BRAND:",B2),4),RIGHT(B2,LEN(B2)-(FIND("SIZE:",B2)+4)))
J2:J3	J2	=IF("LOCO"=H2,RIGHT(B2,LEN(B2)-(FIND("SIZE:",B2)+4)),"")

 

[Ruban S]

What's the "key" to decide if you want ATT V1 versus ATT V2, etc.? Is it "TYPE" and "BRAND" ?

Dear,

I dont want seperate formula for each cell. i need VBA program for the same. With reference to LONG cell description, Data (ATT V) has to split automatically against (ATT N). Same has mentioned in "I need it like this"

 

[kweaver]

If those formulas work for you, then put them into the VBA program that you're using.
Something like this:

Sub Ruban()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("F2:F" & LR).Formula = "=IF(ISERROR(FIND(""TYPE:"",B2)),"""",MID(B2,5+FIND(""TYPE:"",B2),(FIND(""PACK"",B2)-3)-(4+FIND(""TYPE:"",B2))))"
Range("H2:H" & LR).Formula = "=IF(ISERROR(FIND(""TYPE:"",B2)),MID(B2,6+FIND(""BRAND:"",B2),4),RIGHT(B2,LEN(B2)-(FIND(""SIZE:"",B2)+4)))"
Range("J2:J" & LR).Formula = "=IF(""LOCO""=H2,RIGHT(B2,LEN(B2)-(FIND(""SIZE:"",B2)+4)),"""")"
End Sub

 

[Ruban S]

Dear,
This is an example. I have may Rows and columns like this. With respect to ATT N, it has to auto fetch ATT V from LONG. If ATT N is not available in LONG, it has to fill blank or "-" in ATT V.

 

[Ruban S]

Please help me.

 

[Ruban S]

Please let me know if the Thread is not Clear.

 

[Ruban S]

Any additional information needed ?

 

[Ruban S]

Update Please