Extracting data

[richardthelionheart]

I have data which I need to sort based on certain criteria.
Here are a few examples of the data.
1TT66
32144
24TTT
5T153
36335
TTTTT
44T23
So the data may contain no 'T's' or a number of 'T's'.
These occur randomly in the data.
I need to identify only the following:
Those that contain only 1 'T'
Those that contain no 'T's'
I thought that there might be a formula that could be created that would do this for me, rather than create a number of columns and convoluted maths.
I would be very grateful if someone could give me some direction on this problem.

 

[Toadstool]

Use SUBSTITUTE "T" for null and check before and after lengths.

	A	B	C
1TT66	2 T
32144	No T
24TTT	3 T
5T153	1 T
36335	No T
TTTTT	5 T
44T23	1 T

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LEN(SUBSTITUTE(A1,"T",""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1
[/TH]
[TD="align: left"]=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1,"T",""))+1,"No T","1 T","2 T","3 T","4 T","5 T")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I need to identify only the following:
Those that contain only 1 'T'
Those that contain no 'T's'

.. so could it be this variation, copied down.

Excel 365 (Windows) 32 bit
	A	B
1	1TT66
2	32144	0
3	24TTT
4	5T153	1
5	36335	0
6	TTTTT
7	44T23	1
Count T

Cell Formulas
Range	Formula
B1	=LOOKUP(LEN(A1)-LEN(SUBSTITUTE(A1,"T","")),{0,1,2},{0,1,""})

 

[DanteAmor]

I show you a couple of options:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:93.15px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Text</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Option 1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Option 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:left; ">1TT66</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:left; ">32144</td><td >Yes</td><td >Yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:left; ">24TTT</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:left; ">5T153</td><td >Yes</td><td >Yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:left; ">36335</td><td >Yes</td><td >Yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:left; ">TTTTT</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:left; ">44T23</td><td >Yes</td><td >Yes</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(LEN(A2)-LEN(SUBSTITUTE(A2,"T",""))<2,"Yes","")</td></tr><tr><td >C2</td><td >=IF(IFERROR(SEARCH("T*T",A2),"x")="x","Yes","")</td></tr></table></td></tr></table>

 

[DanteAmor]

In fact, if you use the last option you can use "T" or "t".
A shorter option:

=IF(IFERROR(SEARCH("t*t",A2),0),"","Yes")

 

[richardthelionheart]

Many thanks to all the contributors. I was able to use all your posts to move forward.

 

[Peter_SSs]

Many thanks to all the contributors. I was able to use all your posts to move forward.

Glad you found some useful information. Thanks for letting us know.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.