If the string have specific character

[naungsai]

Dear Friends

I have a column with string. I want to find the string containing specific character. For example if the string have D, H, O and R, the result in Yes/No column will show Yes. Otherwise No.

Topics Yes/No
ABCEHOY NO
ABCDEHOR YES
BCDGHMOR YES
ABCDEFG NO


Please help me.

 

[Bilbo_Baggins_Esq]

"For example if the string have D, H, O and R, the result in Yes/No column will show Yes. Otherwise No."

basically, you're lookiing for the INSTR function.
However, as I noted in your wording, you'll want to make sure of you mean AND or OR.
How you make use of the INSTR function, is determined by if you want ALL of the characters, or ANY of the characters.

You should be able to look up the instructions for the INSTR function.

 

[Bilbo_Baggins_Esq]

here is an example:

iif(InStr (1, String to search, string to find,) > 0,True,False)

The value returned is the starting position within the string where the desired character(s) is/are located, if it exists.
If the value does not exist, 0 is returned and thus the test IIF returns false (in this usage).

But again, you'll want to be careful to make clear to the formula, if you intend to define if ALL or ANY of the characters is required.

 

[gemma-the-husky]

but string slicing is fiddly, slow, and not relational

if these characters indicate attributes of some sort, it would probably be easier to have an appropriate number of corresponding yes/no flags to indicate the status of each character

 

[Bilbo_Baggins_Esq]

but string slicing is fiddly, slow, and not relational

if these characters indicate attributes of some sort, it would probably be easier to have an appropriate number of corresponding yes/no flags to indicate the status of each character

Absolutely agree!

 

[Mike375]

This will do it on the basis that D, H, O and R are upper case but can be changed to include both. This is probably overkill but I just happened to be working on it and it should do what you want,

Firstlly, copy and paste this in to a module

Public Function XxNum2(strOriginalString As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String
lngLength = Len(strOriginalString)
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strOriginalString, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If intAscii = 68 Or intAscii = 72 Or intAscii = 79 Or intAscii = 82 Then
strFixed = strFixed & strTheCharacter
End If
Next lngCtr
XxNum2 = strFixed
End Function

Now in a query and assuming the field that contains your date is called [abc] create a calculate field

NewFieldName: IIf(Len(XxNum2([abc]))=4,"Yes","No")

That will give Yes if all 4 characters are there and No if they are not. If one of the characters is present more than once it will give No, in other words if the string has DDHOR it will give No

This

NewFieldName2: IIf(Len(XxNum2([abc]))>0,"Yes","No")

Will give Yes if at least one of the characters are there and aslos if there are multiple instances of the charactyer, thus HH would give Yes

If the characters will sometimes be in lower case then Google search on ASCII and get the numbers for the lower case versions and then add them to this line in the function

If intAscii = 68 Or intAscii = 72 Or intAscii = 79 Or intAscii = 82

or it might be easier to convert the string to all upper case to start with

 

[naungsai]

Dear Friends and Mike375

My string is already converted to Upper case.
I try with the function of Mike375. It works.

Thanks all and Mike for your help.

 

[Mike375]

If you like you can guarantee uppercase by including StrCov in the query which would become

IIf(Len(XxNum2(StrConv([abc],1)))=4,"Yes","No")