1

I have a spreadsheet that contains lists of numbers in string format, a bit like this.

1,2,3
1,2,three
4,five,6
four, five, 6

I would like to convert the words within the string into numbers, so that the data above would look like this:

1,2,3 
1,2,3
4,5,6
4,5,6

Does anyone know if it is possible to do this without using VBA please?

SRJCoding
  • 335
  • 2
  • 15

1 Answers1

1

I have used the following approach in the past, that maybe suitable for your needs.

=LET(SUBST, LAMBDA(txt,tks, REDUCE(txt, SEQUENCE(COLUMNS(tks), 1),
  LAMBDA(ac,idx,SUBSTITUTE(ac,INDEX(tks, 1, idx), INDEX(tks, 2, idx))))),
  SUBST("1,one,2,two,3,three", {"one","two","three";"1","2","3"}))

It returns: 1,1,2,2,3,3.

It uses REDUCE to invoke SUBSTITUTE as many times as tks (tokens) we have. The input variable tks has in the first row the old values and in the second row the corresponding new values (the formula can be easily adapted to have tks in a column-wise array). Showing up to three, but you can complete the rest of tks with the additional values.

You can define SUBST as a LAMBDA function in Name Manager and then use it like any other Excel standard function.

Note: Here How to find and replace multiple values at once in Excel (bulk replace) is documented a recursive approach via the LAMBDA function. Check the section: Example 1. Search and replace multiple words/strings at once.

This solution has several limitations:

  • It uses a volatile function OFFSET
  • Since it uses OFFSET, you can not use arrays, you need to have the old, and new values in a range.
  • The condition for ending the recursion has an empty string in old, so you must ensure it.
  • Since it is based on recursive LAMBDA, you cannot use a LET function (you get a #NAME? error). Instead, you must define it in the Name Manager. It is a limitation for the Excel Web version because you cannot access the Name Manager for this version. You can circumvent this limitation for Excel Web by installing the Add-ins: Advanced Formula Editor and use for example the Module tab.

Based on one of the approaches provided in the previous link, you can define a LAMBDA function in Name Manager and name it for example: MultiReplace

=LAMBDA(text, old, new, IF(old="", text, 
  MultiReplace(SUBSTITUTE(text, old, new), 
    OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

and then invoke it as follow: sample using LAMBDA recursive function

The previous LAMBDA function MultiReplace can be modified to mitigate the limitations mentioned above, such as using OFFSET, so it can work with arrays also as follow:

= LAMBDA(text, old, new, IF(LEN(@old)=0, text, 
  MultiReplace(SUBSTITUTE(text, INDEX(old,1), INDEX(new,1)), 
    IFERROR(DROP(old,1),""), IFERROR(DROP(new,1),""))))

Note: To stop the recurrence we use the @-operator to get the first element, therefore when we have removed all the rows, the length will be empty.

Now we can invoke the function for arrays as follow:

=MultiReplace("1,one,2,two,3,three", {"one";"two";"three"}, {"1";"2";"3"})
David Leal
  • 6,373
  • 4
  • 29
  • 56