How can I replace multiple string at once in Excel?-CodePudding

The function I expected

some_function(original_text, "search_text", "replacement_text")

The value of the second & third parameters will be multiple characters. For example. The result will replace the character based on the location of the character at the second & third parameters

some_function("9528", "1234567890", "abcdefghij")

1 -> a
2 -> b
3 -> c
...
8 -> h
9 -> i
0 -> j

The result of some_function will be iebh. The nested SUBSTITUTE function can archive the goal but I hope to compact the complexity.

CodePudding user response：

The way you described your requirement is best written out via

Formula in A3:

=REDUCE(A1,SEQUENCE(LEN(B1)),LAMBDA(x,y,SUBSTITUTE(x,MID(B1,y,1),MID(C1,y,1))))

Another, more convoluted way, could be:

=LET(A,9528,B,1234567890,C,"abcdefghij",D,MID(A,SEQUENCE(LEN(A)),1),CONCAT(IFERROR(MID(C,FIND(D,B),1),D)))

Or, as per the sceenshot above:

=LET(A,A1,B,B1,C,C1,D,MID(A,SEQUENCE(LEN(A)),1),CONCAT(IFERROR(MID(C,FIND(D,B),1),D)))

CodePudding user response：

Function Multi_Replace(Original As String, Search_Text As String, Replace_With As String) As String

'intEnd represents the last character being replaced
Dim intEnd As Long: intEnd = WorksheetFunction.Min(Len(Search_Text), Len(Replace_With))
'necessary if Search text and replace text are different lengths;

Dim intChar As Long 'to track which character we're replacing

'Replace each character individually
For intChar = 1 To intEnd
    Original = Replace(Original, Mid(Search_Text, intChar, 1), Mid(Replace_With, intChar, 1))
Next

Multi_Replace = Original

End Function

CodePudding user response：

Maybe simpler if you do not have lambda yet: =TEXTJOIN(,,CHAR(96 MID(A1,SEQUENCE(LEN(A1)),1)))

*Note that this will not return 0 as the expected result.

CodePudding user response：

Let's say you have a list of countries in column A and aim to replace all the abbreviations with the corresponding full names. you start with inputting the "Find" and "Replace" items in separate columns (D and E respectively), and then enter this formula in B2:

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

Translated from the Excel language into the human language, here's what the formula does:

Search for the A2 value (lookup_value) in D2:D4 (lookup_array) and return a match from E2:E4 (return_array). If not found, pull the original value from A2.

Double-click the fill handle to get the formula copied to the below cells, and the result won't keep you waiting:

Since the XLOOKUP function is only available in Excel 365, the above formula won't work in earlier versions. However, you can easily mimic this behavior with a combination of IFERROR or IFNA and VLOOKUP:

=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)