Multiple Replace of partial texts

[taviz]

Hello all !!

I need to replace certain partial texts within certain other texts. Since the list is long, I need a way to do it without using Find and Replace.

In the attached workbook, Column A is input, Column B is what I want as output. The "key" table is in columns E and F.

Thanks!

[sandy666]

with Power Query

[Glenn Kennedy]

or with a formula (delete all expected results first):

=LET(R,D2:D4,W,E2:E4,BYROW(A2:A10,LAMBDA(x,SUBSTITUTE(x,LOOKUP(1000,SEARCH(R,x),R),LOOKUP(1000,SEARCH(R,x),W)))))

[HansDouwe]

With an other formula:

Please Login or Register  to view this content.

[Glenn Kennedy]

Having seen Hans'


a tweak:

=LET(R,F2:F4,W,G2:G4,BYROW(A2:A11,LAMBDA(x,IFERROR(SUBSTITUTE(x,LOOKUP(1000,SEARCH(R,x),R),LOOKUP(1000,SEARCH(R,x),W)),x))))

[HansDouwe]

Having seen Glenn's another formula everything in 1 go:

Please Login or Register  to view this content.

[Glenn Kennedy]

Hmmm. I couldn't get it working with MAP. But then, I don't fully (maybe not even partially..) understand it. Time to try to take it apart.

[HansDouwe]

@Glenn,

If you replace in your own formula BYROW by MAP you have a MAP formula working,
because a BYROW formula on 1 column works the same as MAP.

You can also replace in my formula MAP by BYROW.

[Glenn Kennedy]

Ah yes... so it does. But, in some arcane manner (sparrow's entrails... alchemy... transmutation of base metals into gold...) MAP can also work on multicolumn arrays. I think??

[HansDouwe]

Yes, a MAP works also on multicolumn and returns multicolumn, BYROW on multicolumn returns only 1 column.
MAP is actually BYCELL

[Glenn Kennedy]

Thank you! Most solutions that I've seen, which use MAP, have been applied to single columns... and mostly by you. I hadn't really appreciated the similarity/difference between it & BYROW.

Now all I have to do is to find a thread that I can apply it to....

[JEC.]

Here one more variant

Please Login or Register  to view this content.

[T.I.]

One more PQ option.

[FlameRetired]

Can't tell if this works by accident because of the data.

Please Login or Register  to view this content.