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]

TOCO TRISHUL DET TRD 175 AL 10 GR SY				Trishul
TOCO TAI3038 DET TRD 175 AL 10 GR SY IN				TAI3038
TOCO TO-1458 DET TRD 175 AL 10 GR SY		TO-1458			TO-1458
CACO INDRA SWE TRD 175 AL 10 GR SY				INDRA
CFCO KIMAYA TRD 275 AL 10 GR SY				KIMAYA
WCCO BC 90 TRD 175 AL 10 GR SY				BC 90
WCCO BC76 TRD 175 AL 10 GR SY				BC76
OKCO OH-597 TRD 284 TN 250 GR SY		OH-597			NK-3325
CFCO CFH1522 TRD 275 AL 10 GR SY IN				NK-5251
WCCO BC64 TRD 175 AL 10 GR SY				NK-6302
CUCO TASTY SHT TRD 175 AL 10 GR SY
BTCO BG130001 TRD 175 AL 50 GR SY IN
CFCO LUCKY TRD 275 AL 10 GR SY
CUCO CAIH14 LNG TRD 175 AL 10 GR SY
YBCO YB-7 TRD 288 BX 500 GR SY		YB-7
RACO IVORY WHITE TRD 288 TN 250 GR SY
BICO BGH-110 TRD 175 AL 50 GR SY		BGH-110
BTCO BG130001 TRD 175 AL 10 GR SY IN
TOCO HODGE DET TRD 175 AL 10 GR SY
TOCO HEEMSHIKHAR IND TRD 175 AL 10GRSYIN
TOCO HEEMSONA IND TRD 175 AL 10 GR SY
OKCO 152 TRD 284 TN 250 GR SY
TOCO ABHINAV DET TRD 175 AL 10 GR SY
CACO HH04968 HOT TRD 175 AL 10 GR SY
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
BICO BGH-110 TRD 175 AL 25 GR SY		BGH-110
BICO ABHILASH TRD 175 AL 50 GR SY IN
YBCO MITALI TRD 288 PL 500 GR SY
OKCO OH2324 TRD 284 AL 250 GR SY
SGCO SX17 TRD 670 PL 5 KG FT IN
TOCO AVINASH-3 DET TRD 175 AL 10 GR SY		AVINASH-3
CFCO SUHASINI TRD 275 AL 10 GR SY IN
BICO BGH-110 TRD 175 AL 10 GR SY		BGH-110
TOCO AMLIK DET TRD 175 AL 10 GR SY

<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>
[TD="class: xl65, width: 378"]Product List[/TD]
[TD="class: xl65, width: 268"][/TD]
[TD="class: xl65, width: 204"]Your Formula Result[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 153"]Brand Criteria or Brand
[/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

</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.