CODEWITHSUNDEEP
google-sheets
Matan Arie
Matan Arie

Reputation: 11

Removing letters from string in Google Sheets

I have a string SOFTMAAWCKOVENRFLLOW from which I would like to remove the first occurrence of each character in a second string WOLFMAN, which would result in the string STACKOVERFLOW.

Example Sheet

I'm seeking a way to do this in Google Sheets with a single formula, without relying on GAS to create a custom function.

Any ideas?

Upvotes: 1

Views: 853

Answers (5)

Matan Arie
Matan Arie

Reputation: 11

Here's the solution I'm going with. It was posted by Ed(Ohio) here.

  =CONCATENATE(filter(split(regexreplace(A2,"",","),","),
  (countifs(split(regexreplace(A2,"",","),","),
            split(regexreplace(A2,"",","),","),
            SEQUENCE(1, len(A2)), "<=" & SEQUENCE(1, len(A2)))<>1)+
            (ifna(match(split(regexreplace(A2,"",","),","),
                        split(regexreplace(B2,"",","),","),0))=0)))

Thanks everyone for your help.

Upvotes: 0

JvdV
JvdV

Reputation: 75900

Here is a dynamic formula you could try:

enter image description here

Formula in C2:

=INDEX(TEXTJOIN("",1,FILTER(MID(A2,SEQUENCE(LEN(A2)),1),NOT(MATCH(MID(A2,SEQUENCE(LEN(A2)),1),MID(A2,SEQUENCE(LEN(A2)),1),0)=SEQUENCE(LEN(A2))*MMULT(--(MID(A2,SEQUENCE(LEN(A2)),1)=MID(B2,SEQUENCE(1,LEN(B2)),1)),SEQUENCE(LEN(B2),1,1,0))))))

Upvotes: 0

AziMez
AziMez

Reputation: 2082

You can solve this problem with a single formula by trying to combine two useful functions SUBSTITUTE and MID.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2;MID(B2;10;1);"";1);MID(B2;9;1);"";1);MID(B2;8;1);"";1);MID(B2;7;1);"";1);MID(B2;6;1);"";1);MID(B2;5;1);"";1);MID(B2;4;1);"";1);MID(B2;3;1);"";1);MID(B2;2;1);"";1);MID(B2;1;1);"";1)

Here is an Examlpe.

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try:

=JOIN(, FILTER(FLATTEN(SPLIT(REGEXREPLACE(A2&"", "(.)", "$1×"), "×")), 
 NOT(COUNTIF(FLATTEN(SPLIT(REGEXREPLACE(B2&"", "(.)", "$1♦×"), "×"))&
 COUNTIFS(FLATTEN(SPLIT(REGEXREPLACE(B2&"", "(.)", "$1×"), "×")), 
 FLATTEN(SPLIT(REGEXREPLACE(B2&"", "(.)", "$1×"), "×")), 
 SEQUENCE(LEN(B2)), "<="&SEQUENCE(LEN(B2))), 
 FLATTEN(SPLIT(REGEXREPLACE(A2&"", "(.)", "$1♦×"), "×"))&
 COUNTIFS(FLATTEN(SPLIT(REGEXREPLACE(A2&"", "(.)", "$1×"), "×")), 
 FLATTEN(SPLIT(REGEXREPLACE(A2&"", "(.)", "$1×"), "×")), 
 SEQUENCE(LEN(A2)), "<="&SEQUENCE(LEN(A2)))))))

enter image description here

Upvotes: 0

doubleunary
doubleunary

Reputation: 19030

Try something like this to get started:

=substitute( substitute( substitute( substitute( substitute( substitute( substitute( 
  A2, 
  "W", "", 1 ), 
  "O", "", 1 ), 
  "L", "", 1 ), 
  "F", "", 1 ), 
  "M", "", 1 ), 
  "A", "", 1 ), 
  "N", "", 1 )

Upvotes: 1

Related Questions