Charachter Detection in the text and counting them

[Dezom]

Hello Everyone.

In the following table I gave some example texts. For the "Correction Work" tasks, I need to find the number of the works which is written the same tasks without "Correction Work"
So for your better understanding I wrote manually what result should formula give us in the column C.
For example first correction work task,
"ABC Correction Work A45-HGHG Mirror edition" has one time ABC task which is in the row 12 as "ABC A45-HGHG Mirror edition" So formula should give the number for this correction task "1".

Charachter detection and counting formula.xlsx
	A	B	C
1	Tasks	Concepts	What should formula give?
2	ABC Correction Work A45-HGHG Mirror edition	Correction Work	1
3	ABC Correction Work A12-HGHG Suspension adjustment	Correction Work	1
4	ABC A21-A546 Differensial adjustment	ABC
5	ABC Correction Work A32-HGH4 Signal adjustment	Correction Work	0
6	ABC A65-A453 Chair adjustment	ABC
7	ABC 12F-4H89 Leather adjustment	ABC
8	ABC A12-HGHG Suspension adjustment	ABC
9	ABC Correction Work A21-A546 Differensial adjustment	Correction Work	1
10	ABC A28-9834 Glass Adjustment	ABC
11	ABC Correction Work A65-HBKL Instrumental adjustment	Correction Work	1
12	ABC A45-HGHG Mirror edition	ABC
13	ABC Correction Work A21-JAXY Backskeleton adjustment	Correction Work	0
14	ABC A65-HBKL Instrumental adjustment	ABC
15	ABC Correction Work 12F-4H89 Leather adjustment	Correction Work	1
16	ABC Correction Work A28-9834 Glass Adjustment	Correction Work	0
17	ABC Correction A25-4RTG Light adjustment	Correction Work	0
18	ABC Correction Work A65-A453 Chair adjustment	Correction Work	1
Data

 

[Peter_SSs]

Let's see if this is headed in the right direction. I do get two different answers to you.
1. For cell C16 why isn't it 1, counting A10?
2. In cell C17 I got 1 instead of 0 but that is because A17 does not have 'Correction Work' in it and you said

number of the works which is written the same tasks without "Correction Work"

20 09 06.xlsm
	A	B	C
1	Tasks	Concepts	What should formula give?
2	ABC Correction Work A45-HGHG Mirror edition	Correction Work	1
3	ABC Correction Work A12-HGHG Suspension adjustment	Correction Work	1
4	ABC A21-A546 Differensial adjustment	ABC
5	ABC Correction Work A32-HGH4 Signal adjustment	Correction Work	0
6	ABC A65-A453 Chair adjustment	ABC
7	ABC 12F-4H89 Leather adjustment	ABC
8	ABC A12-HGHG Suspension adjustment	ABC
9	ABC Correction Work A21-A546 Differensial adjustment	Correction Work	1
10	ABC A28-9834 Glass Adjustment	ABC
11	ABC Correction Work A65-HBKL Instrumental adjustment	Correction Work	1
12	ABC A45-HGHG Mirror edition	ABC
13	ABC Correction Work A21-JAXY Backskeleton adjustment	Correction Work	0
14	ABC A65-HBKL Instrumental adjustment	ABC
15	ABC Correction Work 12F-4H89 Leather adjustment	Correction Work	1
16	ABC Correction Work A28-9834 Glass Adjustment	Correction Work	1
17	ABC Correction A25-4RTG Light adjustment	Correction Work	1
18	ABC Correction Work A65-A453 Chair adjustment	Correction Work	1
Sheet2 (2)

Cell Formulas
Range	Formula
C2:C18	C2	=IF(B2="Correction Work",COUNTIF(A$2:A$18,TRIM(SUBSTITUTE(A2,"Correction Work",""))),"")

 

[CA_Punit]

Hi Try This

Book1
	A	B	C	D
1	Tasks	Concepts	What should formula give?	Formula
2	ABC Correction Work A45-HGHG Mirror edition	Correction Work	1	1
3	ABC Correction Work A12-HGHG Suspension adjustment	Correction Work	1	1
4	ABC A21-A546 Differensial adjustment	ABC		0
5	ABC Correction Work A32-HGH4 Signal adjustment	Correction Work	0	0
6	ABC A65-A453 Chair adjustment	ABC		0
7	ABC 12F-4H89 Leather adjustment	ABC		0
8	ABC A12-HGHG Suspension adjustment	ABC		0
9	ABC Correction Work A21-A546 Differensial adjustment	Correction Work	1	1
10	ABC A28-9834 Glass Adjustment	ABC		0
11	ABC Correction Work A65-HBKL Instrumental adjustment	Correction Work	1	1
12	ABC A45-HGHG Mirror edition	ABC		0
13	ABC Correction Work A21-JAXY Backskeleton adjustment	Correction Work	0	0
14	ABC A65-HBKL Instrumental adjustment	ABC		0
15	ABC Correction Work 12F-4H89 Leather adjustment	Correction Work	1	1
16	ABC Correction Work A28-9834 Glass Adjustment	Correction Work	0	1
17	ABC Correction A25-4RTG Light adjustment	Correction Work	0	0
18	ABC Correction Work A65-A453 Chair adjustment	Correction Work	1	1
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=SUM(IFERROR(SEARCH("ABC "&MID(SUBSTITUTE(A2,"ABC Correction Work ",""),1,SEARCH(" ",SUBSTITUTE(A2,"ABC Correction Work ",""))),$A$2:$A$18)^0,0))

It has some Condition
1. Work Id such as A45-HGHG should alway start with ABC in case of No correction work

 

[Dezom]

Let's see if this is headed in the right direction. I do get two different answers to you.
1. For cell C16 why isn't it 1, counting A10?
2. In cell C17 I got 1 instead of 0 but that is because A17 does not have 'Correction Work' in it and you said

20 09 06.xlsm
	A	B	C
1	Tasks	Concepts	What should formula give?
2	ABC Correction Work A45-HGHG Mirror edition	Correction Work	1
3	ABC Correction Work A12-HGHG Suspension adjustment	Correction Work	1
4	ABC A21-A546 Differensial adjustment	ABC
5	ABC Correction Work A32-HGH4 Signal adjustment	Correction Work	0
6	ABC A65-A453 Chair adjustment	ABC
7	ABC 12F-4H89 Leather adjustment	ABC
8	ABC A12-HGHG Suspension adjustment	ABC
9	ABC Correction Work A21-A546 Differensial adjustment	Correction Work	1
10	ABC A28-9834 Glass Adjustment	ABC
11	ABC Correction Work A65-HBKL Instrumental adjustment	Correction Work	1
12	ABC A45-HGHG Mirror edition	ABC
13	ABC Correction Work A21-JAXY Backskeleton adjustment	Correction Work	0
14	ABC A65-HBKL Instrumental adjustment	ABC
15	ABC Correction Work 12F-4H89 Leather adjustment	Correction Work	1
16	ABC Correction Work A28-9834 Glass Adjustment	Correction Work	1
17	ABC Correction A25-4RTG Light adjustment	Correction Work	1
18	ABC Correction Work A65-A453 Chair adjustment	Correction Work	1
Sheet2 (2)

Cell Formulas
Range	Formula
C2:C18	C2	=IF(B2="Correction Work",COUNTIF(A$2:A$18,TRIM(SUBSTITUTE(A2,"Correction Work",""))),"")

Hi Peter thats perfectly working thanks. For those two points you mentioned. You are right I did manual mistakes. Thanks

 

[Dezom]

Hi Try This

Book1
	A	B	C	D
1	Tasks	Concepts	What should formula give?	Formula
2	ABC Correction Work A45-HGHG Mirror edition	Correction Work	1	1
3	ABC Correction Work A12-HGHG Suspension adjustment	Correction Work	1	1
4	ABC A21-A546 Differensial adjustment	ABC		0
5	ABC Correction Work A32-HGH4 Signal adjustment	Correction Work	0	0
6	ABC A65-A453 Chair adjustment	ABC		0
7	ABC 12F-4H89 Leather adjustment	ABC		0
8	ABC A12-HGHG Suspension adjustment	ABC		0
9	ABC Correction Work A21-A546 Differensial adjustment	Correction Work	1	1
10	ABC A28-9834 Glass Adjustment	ABC		0
11	ABC Correction Work A65-HBKL Instrumental adjustment	Correction Work	1	1
12	ABC A45-HGHG Mirror edition	ABC		0
13	ABC Correction Work A21-JAXY Backskeleton adjustment	Correction Work	0	0
14	ABC A65-HBKL Instrumental adjustment	ABC		0
15	ABC Correction Work 12F-4H89 Leather adjustment	Correction Work	1	1
16	ABC Correction Work A28-9834 Glass Adjustment	Correction Work	0	1
17	ABC Correction A25-4RTG Light adjustment	Correction Work	0	0
18	ABC Correction Work A65-A453 Chair adjustment	Correction Work	1	1
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=SUM(IFERROR(SEARCH("ABC "&MID(SUBSTITUTE(A2,"ABC Correction Work ",""),1,SEARCH(" ",SUBSTITUTE(A2,"ABC Correction Work ",""))),$A$2:$A$18)^0,0))

It has some Condition
1. Work Id such as A45-HGHG should alway start with ABC in case of No correction work

Thanks

 

[Peter_SSs]

Hi Peter thats perfectly working thanks. For those two points you mentioned. You are right I did manual mistakes. Thanks

OK, great. Thanks for the follow-up.