Forum Discussion
How to find the difference between two text cells
=TRIM(SUBSTITUTE(A2,B2,""))
Hi PeterBartholomew1, This sounds exactly like something I currently need. Could you explain how to insert this into a cell / table column? Thanks a lot!
With kind regards,
Patrick
To keep the worksheet as clear as possible, I used
= MAP(Main, Secondary, Removeλ)as the worksheet formula. I then used defined Names to refer to the two Ranges (a column of full texts strings and a column of strings to remove). The Lambda functions Removeλ and SUBSTλ are also defined using Name Manager or the AFE (Advanced Formula Editor).
If you prefer to minimise your use of Name Manager, an alternative exists of defining these LAMBDA functions within LET as a worksheet formula.
= LET(
Removeλ, LAMBDA(string,sub, REDUCE(string, TEXTSPLIT(sub, " "), SUBSTλ)),
SUBSTλ, LAMBDA(x,y, TRIM(SUBSTITUTE(x, y, ""))),
MAP(Main, Secondary, Removeλ)
)If you are using Tables and Structured References, the references to the strings become
[@Main] and [@Secondary] and Removeλ can be used directly without the MAP.
You only need to make one of these work, but I do not know which you might prefer!
I have just noted that the last comment about Excel Tables was somewhat cryptic. I think tea was on the table! Inside a table a Spill range would error, instead one uses single cell relative references and relies upon the Table defaults to replicate the formula down the column.
The formula is
= LET( SUBSTλ, LAMBDA(x,y, TRIM(SUBSTITUTE(x, y, ""))), Removeλ, LAMBDA(string,sub, REDUCE(string, TEXTSPLIT(sub, " "), SUBSTλ)), Removeλ([@Name], [@Secondary]) )If the two Lambda functions are pre-defined as Defined Names then only the final line of code need appear on the worksheet.