How can I replace multiple string at once in Excel?

Question

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.

JvdV · Accepted Answer · 2022-02-16 09:26:30Z

7

The way you described your requirement is best written out via REDUCE(), a lambda-related helper function and recently announced to be in production:

=REDUCE("9528",SEQUENCE(10),LAMBDA(x,y,SUBSTITUTE(x,MID("1234567890",y,1),MID("abcdefghij",y,1))))

Needless to say, this would become more vivid when used with cell-references:

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)))

edited Feb 16, 2022 at 9:26

answered Feb 16, 2022 at 8:48

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Trudy Over a year ago

Came to propose my own solution, but found yours (with LET, as I don't have LAMBDA) better.

Spencer Barnes · Accepted Answer · 2022-02-16 08:47:25Z

0

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

answered Feb 16, 2022 at 8:47

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

1 Comment

Spencer Barnes Over a year ago

note that this does the characters in order, one after the other, not all at once. Therefore the output of Multi_replace("some text here", "abcdefghijklmnopqrstuvwxy", "bcdefghijklmnopqrstuvwxyz") would be zzzz zzzz zzzz because first it corrects a's to b's then those b's to c's, etc etc

P.b · Accepted Answer · 2022-02-16 11:57:10Z

0

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.

answered Feb 16, 2022 at 11:57

P.b

12k2 gold badges15 silver badges34 bronze badges

Comments

Gaston Alex · Accepted Answer · 2022-02-16 08:47:42Z

-3

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)

answered Feb 16, 2022 at 8:47

Gaston Alex

1671 silver badge6 bronze badges

3 Comments

tnavidi Over a year ago

This doesn't seem to answer the question, which is how to replace characters in a string

Gaston Alex Over a year ago

=REPLACE(old_text, start_num, num_chars, new_text) old_text is the cell containing the text you want to replace. start_num tells Excels where the text you want to replace starts within the cell. num_chars is the number of characters you want to replace. new_text is the text you’d like to replace that number of characters.

tnavidi Over a year ago

The result of some_function("9528, "1234567890", "abcdefghij") is supposed to be iebh, (neither XLOOKUP nor REPLACE will do that.)

Collectives™ on Stack Overflow

How can I replace multiple string at once in Excel?

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related