CODEWITHSUNDEEP
excelexcel-formula
Manu
Manu

Reputation: 819

Filter characters with function

What is the simplest way to substring character that already appear on the line ?

Is that possible to do this with function (means no VBA) ?

More information :

If I have cells like 

   *A* *B* *C* *D* *E* *F*
  |        DATA           |  RESULT    |
1 | A | B | C |   | E | F |       D
2 | C |   |   |   | E | F |       ABD

Is there a function that can give this result :

fx("ABCEF", "ABCDEF") returns D
fx("CF", "ABCDEF") returns ABDE

Upvotes: 1

Views: 124

Answers (3)

pnuts
pnuts

Reputation: 59450

I doubt the simplest way (or at least not the shortest), but certainly possible without VBA: 

=IF(ISERROR(MATCH("A",A2:F2,0)),"A","")&IF(ISERROR(MATCH("B",A2:F2,0)),"B","")&IF(ISERROR(MATCH("c",A2:F2,0)),"C","")&IF(ISERROR(MATCH("D",A2:F2,0)),"D","")&IF(ISERROR(MATCH("E",A2:F2,0)),"E","")&IF(ISERROR(MATCH("F",A2:F2,0)),"F","")

Upvotes: 1

brettdj
brettdj

Reputation: 55672

It is possible that a complex formula acting as an array may be able to do this - but on a coding site, I'd keep it simple and run a VBA UDF

Sub Test()
Debug.Print Updated("ABCEF", "ABCDEF")
Debug.Print Updated("CF", "ABCDEF")
End Sub

function

Function Updated(strRep As String, strIn As String)
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "[" & strRep & "]"
.Global = True
Updated = .Replace(strIn, vbNullString)
End With
End Function

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Try this small UDF:

Public Function WhatsLeft(LittleString As String, BigString As String) As String
    WhatsLeft = BigString
    For i = 1 To Len(LittleString)
        c = Mid(LittleString, i, 1)
        WhatsLeft = Replace(WhatsLeft, c, "")
    Next i
End Function

Upvotes: 0

Related Questions