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

noone · August 27, 2022, 12:53pm

Hello, new here

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

ex:


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!

Lupp · August 28, 2022, 12:43pm

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)

noone · August 28, 2022, 2:36pm

are you coding with your feets?!

karolus · August 27, 2022, 9:12pm

Hallo

=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

noone · August 27, 2022, 11:23pm

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 : i.imgur.com/4E1wYa6.png

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

karolus · August 28, 2022, 5:22am

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)

noone · August 28, 2022, 7:17am

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?

sokol92 · August 28, 2022, 10:17am

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
 s=exp
 For i=1 To Ubound(arr)
   s=Replace(s, arr(i,1), arr(i, 2))
 Next i
 MultiReplace=s  
End Function

For an example from @karolus:

=MULTIREPLACE(A1; $C$1:$D$4)

mariosv · August 28, 2022, 11:35am

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

=TEXTJOIN("+";1;IFERROR(VLOOKUP(REGEX(A4;"([^+]+)";;ROW($1:$99));$C$1:$D$99;2;0);IFERROR(REGEX(A4;"([^+]+)";;ROW($1:$99));"")))

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.

Lupp · August 28, 2022, 12:46pm

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

noone · August 28, 2022, 2:27pm

hi, thank you!

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

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

noone · August 28, 2022, 2:37pm

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

erAck · August 27, 2022, 9:15pm

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.

LeroyG · August 28, 2022, 4:43pm

And to get a string, use
=name1&"+"&name2&"+"&name42
or
=TEXTJOIN("+";0;name1;name2;name42).

words_to_numbers_string.ods (8.6 KB)