Pull Text Only String "PT" & "CV"

[muhammad susanto]

hi all....

i want to extract/pull text and get string only "PT" and "CV" 2 character from left text

[TABLE="width: 1, align: left"]
<tbody>[TR]
[TH="bgcolor: #FFFF00"]SAMPLE[/TH]
[TH="bgcolor: #FFFF00"]EXPECTED RESULT[/TH]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]CV. SINAR TERANG TERHUTANG[/TD]
[TD="bgcolor: #FFFFFF"]CV[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]PT OLYMPIA VENEER PRODUCT[/TD]
[TD="bgcolor: #FFFFFF"]PT[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]PT SUMATRA TIMUR INDONESIA PROYEK [/TD]
[TD="bgcolor: #FFFFFF"]PT[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]PT. SHIELDS INDONESIA[/TD]
[TD="bgcolor: #FFFFFF"]PT[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]CV JASA KARYA[/TD]
[TD="bgcolor: #FFFFFF"]CV[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]RAHIMAH / CEMPAKA AYU[/TD]
[TD="bgcolor: #FFFFFF"]blank[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]DWIYONO SUBIANTOSO / MITRA ABADI [/TD]
[TD="bgcolor: #FFFFFF"]blank[/TD]
[/TR]
</tbody>[/TABLE]













any assistance, much appreciated..

m.susanto

 

[Fishboy]

hi all....

i want to extract/pull text and get string only "PT" and "CV" 2 character from left text

Will it always be either PT, CV or nothing? If so the following formula will do the trick (assuming your list starts in A1):

=IF(LEFT(A1,2)="CV","CV",IF(LEFT(A1,2)="PT","PT",""))

This can be drag-filled down the length of your list.

 

[Comfy]

Excel 2010
	A	B
1	CV. SINAR TERANG TERHUTANG	CV
2	PT OLYMPIA VENEER PRODUCT	PT
3	PT SUMATRA TIMUR INDONESIA PROYEK	PT
4	PT. SHIELDS INDONESIA	PT
5	CV JASA KARYA	CV
6	RAHIMAH / CEMPAKA AYU DWIYONO SUBIANTOSO / MITRA ABADI
Sheet1

Cell Formulas
Range	Formula
B1	=CHOOSE(IFERROR(MATCH(1,FIND({"CV","PT"},LEFT(A1,2)),0),0)+1,"","CV","PT")

 

[FormR]

Just another possible option:

=IF(OR(LEFT(A1,2)={"CV","PT"}),LEFT(A1,2),"")

 

[muhammad susanto]

hi guys....all work great!!!

many thanks...

 

[muhammad susanto]

hi guys...all of you, working great!!!

many thanks...

 

[muhammad susanto]

hi guys...i have a new problem this below :

[TABLE="align: left"]
<tbody>[TR]
[TH][/TH]
[TH][/TH]
[/TR]
</tbody>[/TABLE]


[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]PT. SHIELDS INDONESIA[/TH]
[TH]PT[/TH]
[/TR]
[TR]
[TD]CV JASA KARYA[/TD]
[TD]CV[/TD]
[/TR]
[TR]
[TD]SHIELDS INDONESIA, PT[/TD]
[TD]PT[/TD]
[/TR]
[TR]
[TD]JASA KARYA, CV[/TD]
[TD]CV[/TD]
[/TR]
[TR]
[TD]SHIELDS INDONESIA[/TD]
[TD]blank[/TD]
[/TR]
</tbody>[/TABLE]



have you great idea?

 

[gaz_chops]

Tweaking Comfy's solution.

=IFERROR(CHOOSE(MATCH(TRUE,ISNUMBER(FIND({"CV","PT"},A1)),0),"CV","PT"),"")

 

[muhammad susanto]

Tweaking Comfy's solution.

=IFERROR(CHOOSE(MATCH(TRUE,ISNUMBER(FIND({"CV","PT"},A1)),0),"CV","PT"),"")

not fully work for :

SHIELDS INDONESIA----#N/A---should be blank cell

 

[gaz_chops]

Check my reply again I amended it to add iferror.