Excel FORMULA: Find if all the characters in a cell are in another cell even if in this another cell they are more characters

0
votes

EXCEL FORMULA: Find if all the characters in a cell are in another cell even if in this another cell they are more characters. Example: A1 contains TRSA - B1 contains JHTHASUBR so return TRUE: all characters in A1 are in B1 even if they are not in order or B1 had more characters.

I don't know if I can do that with a formula in excel? Thank you.

1
excelexcel-formula
it is possible with a formula, but quite complicated, you would be better off with VBA solution – Aprillion
It is unlikely that you will find an efficient method of doing this without VBA. – user4039065

1 Answers

1
votes

Try the following User Defined Function:

Public Function IsItInThere(r1 As Range, r2 As Range) As Boolean
   Dim v1 As String, v2 As String, CH As String
   Dim i As Long

   IsItInThere = False
   v1 = r1.Text
   v2 = r2.Text

   For i = 1 To Len(v1)
      CH = Mid(v1, i, 1)
      If InStr(v2, CH) = 0 Then
         Exit Function
      End If
   Next i
   IsItInThere = True
End Function

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=IsItInThere(A1,B1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!