Text formula

[shuklaankur281190]

Hi,
Thanks for your videos. I have a question as below.

I have a table containing some products.
RCCO NK-3325 TRD 633 PL 1 KG NK IN
RCCO NK-3325 TRD 633 PL 2 KG NK IN
RCCO NK-5251 TRD 633 PL 1 KG NK IN
RCCO NK-5251 TRD 633 PL 2 KG NK IN
RCCO NK-6302 TRD 633 PL 1 KG NK IN

Brand is
NK-3325
NK-5251
NK-6302

NK is a brand and after NK digits are verity. Almost 20000 rows are filled with the same as above. Now i want to put a formula for getting Brand and verity only. Some times Brand and verity somewhere in middle.

Please help me.

 

[Rick Rothstein]

I have a table containing some products.
RCCO NK-3325 TRD 633 PL 1 KG NK IN
RCCO NK-3325 TRD 633 PL 2 KG NK IN
RCCO NK-5251 TRD 633 PL 1 KG NK IN
RCCO NK-5251 TRD 633 PL 2 KG NK IN
RCCO NK-6302 TRD 633 PL 1 KG NK IN

Brand is
NK-3325
NK-5251
NK-6302

NK is a brand and after NK digits are verity. Almost 20000 rows are filled with the same as above. Now i want to put a formula for getting Brand and verity only. Some times Brand and verity somewhere in middle.

Is the band and verity always located between the first and second space?

 

[shuklaankur281190]

sometimes it can be anywhere. that is why i am in trouble.

 

[Rick Rothstein]

sometimes it can be anywhere. that is why i am in trouble.

Will it be the only the field with a dash in it?

 

[shuklaankur281190]

Yes with dash

 

[Rick Rothstein]

Yes with dash

Okay, one more question... are there always two characters before the dash and 4 characters (digits) after it?

 

[shuklaankur281190]

Mr.Rick ,No before dash and after dash characters ( digits) count can be change

 

[Rick Rothstein]

Mr.Rick ,No before dash and after dash characters ( digits) count can be change

Okay, give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1,FIND("-",A1))-1)," ",REPT(" ",200)),200))

 

[shuklaankur281190]

Product List		Your Formula Result			Brand Criteria or Brand
TOCO TRISHUL DET TRD 175 AL 10 GR SY		#VALUE!			Trishul
TOCO TAI3038 DET TRD 175 AL 10 GR SY IN		#VALUE!			TAI3038
TOCO TO-1458 DET TRD 175 AL 10 GR SY		TO-1458			TO-1458
CACO INDRA SWE TRD 175 AL 10 GR SY		#VALUE!			INDRA
CFCO KIMAYA TRD 275 AL 10 GR SY		#VALUE!			KIMAYA
WCCO BC 90 TRD 175 AL 10 GR SY		#VALUE!			BC 90
WCCO BC76 TRD 175 AL 10 GR SY		#VALUE!			BC76
OKCO OH-597 TRD 284 TN 250 GR SY		OH-597			NK-3325
CFCO CFH1522 TRD 275 AL 10 GR SY IN		#VALUE!			NK-5251
WCCO BC64 TRD 175 AL 10 GR SY		#VALUE!			NK-6302
CUCO TASTY SHT TRD 175 AL 10 GR SY		#VALUE!
BTCO BG130001 TRD 175 AL 50 GR SY IN		#VALUE!
CFCO LUCKY TRD 275 AL 10 GR SY		#VALUE!
CUCO CAIH14 LNG TRD 175 AL 10 GR SY		#VALUE!
YBCO YB-7 TRD 288 BX 500 GR SY		YB-7
RACO IVORY WHITE TRD 288 TN 250 GR SY		#VALUE!
BICO BGH-110 TRD 175 AL 50 GR SY		BGH-110
BTCO BG130001 TRD 175 AL 10 GR SY IN		#VALUE!
TOCO HODGE DET TRD 175 AL 10 GR SY		#VALUE!
TOCO HEEMSHIKHAR IND TRD 175 AL 10GRSYIN		#VALUE!
TOCO HEEMSONA IND TRD 175 AL 10 GR SY		#VALUE!
OKCO 152 TRD 284 TN 250 GR SY		#VALUE!
TOCO ABHINAV DET TRD 175 AL 10 GR SY		#VALUE!
CACO HH04968 HOT TRD 175 AL 10 GR SY		#VALUE!
RCCO NK-3325 TRD 633 PL 1 KG NK IN		NK-3325
RCCO NK-5251 TRD 633 PL 1 KG NK IN		NK-5251
RCCO NK-5251 TRD 633 PL 1 KG NK IN		NK-5251
RCCO NK-5251 TRD 633 PL 2 KG NK IN		NK-5251
RCCO NK-6302 TRD 633 PL 1 KG NK IN		NK-6302
WACO DRAGON KING SDD TRD 175 AL 50 GR SY		#VALUE!
BICO BGH-110 TRD 175 AL 25 GR SY		BGH-110
BICO ABHILASH TRD 175 AL 50 GR SY IN		#VALUE!
YBCO MITALI TRD 288 PL 500 GR SY		#VALUE!
OKCO OH2324 TRD 284 AL 250 GR SY		#VALUE!
SGCO SX17 TRD 670 PL 5 KG FT IN		#VALUE!
TOCO AVINASH-3 DET TRD 175 AL 10 GR SY		AVINASH-3
CFCO SUHASINI TRD 275 AL 10 GR SY IN		#VALUE!
BICO BGH-110 TRD 175 AL 10 GR SY		BGH-110
TOCO AMLIK DET TRD 175 AL 10 GR SY		#VALUE!

<colgroup><col style="mso-width-source:userset;mso-width-alt:13824;width:284pt" width="378"> <col style="mso-width-source:userset;mso-width-alt:9801;width:201pt" width="268"> <col style="mso-width-source:userset;mso-width-alt:7460;width:153pt" width="204"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:5595;width:115pt" width="153"> </colgroup><tbody>
</tbody>

Mr. Rick your formula is nice but i wanna match my creiteria with product list if creiteria word match with prodect list then answer should be Brand Name.

Hope now you unterstood what i want. I also want to send you my excel book but i don't know how to share to you

 

[Rick Rothstein]

Mr. Rick your formula is nice but i wanna match my creiteria with product list if creiteria word match with prodect list then answer should be Brand Name.

Hope now you unterstood what i want. I also want to send you my excel book but i don't know how to share to you

Show us the table you posted but with a column showing us the exact results you want from the formula.