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.
Best Answer
Yes you need additoinally add a regexextract step: