Search a set of words in a string and replace any occurences with their corresponding definitions

Hello, new here :smiley:

I guess I need some help, I spend too long time to find a solution…

Given a string “name1+name2+name42” in a cell and a range of 2 columns defining values for each words

name1   | 1
name2   | 2
...     |
name42  | 63
...     |

I would like to get the corresponding substitution 1+2+63 with a worksheet formula.

I tried different approaches with regex, substitute, index, match, nesting them etc ! with no luck, all I found is how to search a value in a range of cell but not a set of values inside the content of a cell to apply multiple replacements. (I’m used to VBA and that would be easy but to learn and setup LO macros is another story!..)


A1                   B1 (expected)  C1     D1
name1+name2+name42   1+2+63         name1   1
name3                23             name2   2
name1+name42+unk     1+63+unk       ...
                                    name42  63

Thank you!

This was the first question of a new user of this site.
That’s not the only reason, but surely a allowable one to suspect it of a doubtable background.
Users not exactly experienced with Callc or (worse) mislead by diffuse information about what’s “common” in the Excel universe may not sufficiently consider the need of developing a clear concept before starting the design of sheets.

  • Yes. I actually considered the question, and came to the conclusion that it should either be conceptually transformed as @erAck suggested (including a subsequent solution), or solved by user code in an explicitly sequential way allowing for all the probably needed decisions depending on aspects of the use-case.
  • In specific I tried to regard the application to formulas trying to get working ones.
  • See attachment: verySpecialReplaceInFormulas.ods (13.4 KB)

are you coding with your feets?! :crazy_face:


=TEXTJOIN("+";1;IFNA(VLOOKUP(REGEX(A1;"[^+]+"; ;ROW(A$1:A$10));$C$1:$D$100;2;0);""))

entered as Matrixformula with <ctrl><shift><enter>
solves partially, instead B3:1+63+unk only 1+63

Thanks for the idea but I don’t see how to implement it. I paste formula in B1 and drop down to end of column but it output only value of the first string variable, see screen capture :

nice idea but it can take some time when you have a lot of lines :wink:

As I already said, you need to enter the formula with <ctrl><shift><enter>
or with the [x]Matrix.option in the Formulawizard

asklibre_81168.ods (10.1 KB)

o-k! found how to handle it while something seems odd:

If I do as explained in doc and forums:

  • first select range where to unfold the array formula (here B1-B10 for example)
  • either press f2 (or open formula wizard)
  • paste formula
  • then ctrl+shift+enter (or tick ‘matrix formula’ in the wizard)

In this case the first line is repeated from B1 to B10 without incrementing cell references (Ai here) and same result is repeated 10 times.

Then, thanks to your file if I edit the array formula range (ctrl+/) I see it is only the first cell (B1). So, what is working here is:

  • select only B1
  • ctrl+shift+enter the formula
  • then standard copy/paste B1 in B2, B3, B4… (or double click the fill handle square) it will unfold a 1x1 array formula while incrementing cell references as usual.

Any idea what I’m doing wrong or is it standard behavior?

In such cases, the UDF function is quite appropriate (add to any module of document Standard library).

' Multiple substitution.
' - exp Expression.
' - arr Array (two columns) with data to replace.
Function MultiReplace(exp, arr)
 Dim s As String, i As Long
 For i=1 To Ubound(arr)
   s=Replace(s, arr(i,1), arr(i, 2))
 Next i
End Function

For an example from @karolus:


‘+unk’ can be solved with a second IFERROR:


This formulas doesn’t work on excel files, REGEX function (thanks @erAck), it’s not implemented in excel, it also doesn’t support regular expressions.

Sorry. I hadn’t studied this post before I wrote my comment on the question.

hi, thank you!

totally agree but didn’t want to dig into LO basic :yum:

thanks, nice to you!
I also ‘trim’ the regex in case of spaces around the ‘+’

ok, didn’t see your 2nd comment at first…

Assign names to the value cells in the second column, e.g. move to D1, Shift+Ctrl+T, type name1, hit Enter, move to D2, Shift+Ctrl+T, type name2, hit Enter, …
Then you can use the names in formula expressions, like =name1+name2+name42 for which you can just find&replace column A inserting a = character to generate a formula expression of the string.

1 Like

And to get a string, use

words_to_numbers_string.ods (8.6 KB)