1

Suppose I have a list of people with unique first and last names, in two columns, B and C. See example spreadsheet here.

Cell E6 contains the following text:

Firstname1 would like to say hello to Firstname2. Firstname3 also says hello.

I would like to write an in-cell formula (i.e., not using custom functions) that goes through the text and replaces first names with the corresponding last names. The result should look like this:

Lastname1 would like to say hello to Lastname2. Lastname3 also says hello.

I tried the following, but it seems not to work:

=REGEXREPLACE(E6,"(Firstname[0-9])",VLOOKUP("$1",B:C,2))

Any ideas? It's important that the solution not use a custom formula.

0

Yes you need additoinally add a regexextract step:

=regexreplace(A1,"(Firstname[0-9])",VLOOKUP(regexextract( A1,"Firstname[0-9]"),B:C,2,false))

enter image description here

0
=ARRAYFORMULA(JOIN(" ",IFERROR(VLOOKUP(SPLIT(E6," "),B:C,2,0),SPLIT(E6," "))))
  • SPLIT E6 by space to give a array
  • VLOOKUP the corresponding words in B:C
  • IF #N/A, Use the SPLITted array
  • JOIN back the modified array

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.