Find Char (one of) in String

[RayBan]

Is there an elegant way to see if a string contains ONE "A" (instead of more than one)?

 

[DushiPunda]

Via formula:
=LEN(A1)-LEN(SUBSITUTE(A1,"A",""))

So..

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"A","")) = 1,Do Something If True,Do Something If False)

if you want it to be case sensitive...good question.

if you want to do it via VBA:

If Len(Range("A1").Value) - Len(Replace(Range("A1").Value, "a", "")) = 1 Then
Do Something If True
Else
Do Something If False
End If

 

[AliGW]

This works, although it might not be elegant enough for you:

Excel 2016 (Windows) 32 bit

	A	B
1	Banana	TRUE

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))=1,TRUE,FALSE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

This works, although it might not be elegant enough for you:

Excel 2016 (Windows) 32 bit

	A	B
1	Banana	TRUE

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))=1,TRUE,FALSE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

If the goal is to return TRUE or FALSE, then this is enough...

=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))>1

 

[Gravitas]

This will help you.

https://exceljet.net/formula/split-text-string-at-specific-character

 

[Rick Rothstein]

If the goal is to return TRUE or FALSE, then this is enough...

=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))>1

I can't believe I typed a greater than symbol instead of an equal sign!

This is what I meant to write...

=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))=1

 

[AliGW]

Neither can I, Rick, but it's exactly what I did first time - notice that I edited my post just before you quoted it!!!

 

[RayBan]

Thanks very much, one and all. It was extremely helpful. I wasn't using a worksheet and never thought to say so. (Sorry)
But from your advice I have

Function ContainsJustOne(testString As String, Char As String) As Boolean
    If Len(testString) - Len(Replace(testString, Char, "")) = Len(Char) Then ContainsJustOne = True
End Function

It seems to be working just great and with the added bonus of being case sensitive.
Changing to '=Len(Char)' from '=1' so far tests ok where Char > 1. (Wasn't wanted but makes it better)
Hopefully I have it right!

Thanks all.