Function to repeatedly replace string one-at-a-time via a lookup table

[smallxyz]

	A	B	C	D	E	F
1	a1	a2	a3		@ + @＋＠	a1+a2+a3
2	b1	b2	b3		@ + @＋＠	b1+b2+b3
3	c1	c2	c3		@ + @＋＠	c1+c2+c3

Suppose I have some texts at range "E1:E3", with the value "@+@+@".

I also have another lookup table ranged at "A1:C3"

I hope that, for each output row in column "F", the "@" character in the column "E" text will be replaced one at a time correspondingly by each lookup value in the row table, following a left-to-right column-wise direction in a row, as shown in the above table

e.g. in cell "F1", one possible formula can be =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1,A1,"",1),B1,"",1),C1,"",1)

But as you can see, this formula is very cumbersome and inflexible in case I wish to expand the lookup table to 4 or more columns.



It will be great if there is a much neater and flexible formula in this case.

Thanks.

 

[awoohaw]

if you have 365 (please update your profile, so your version appears on your button).

=TextJoin(" + ",,B2:D2)

Book1
	A	B	C	D	E	F	G
1	1	a1	a2	a3		@ + @＋＠	a1 + a2 + a3
2	2	b1	b2	b3		@ + @＋＠	b1 + b2 + b3
3	3	c1	c2	c3		@ + @＋＠	c1 + c2 + c3
Sheet3

Cell Formulas
Range	Formula
G1:G3	G1	=TEXTJOIN(" + ",,B1:D1)

 

[smallxyz]

if you have 365 (please update your profile, so your version appears on your button).

=TextJoin(" + ",,B2:D2)

Book1
	A	B	C	D	E	F	G
1	1	a1	a2	a3		@ + @＋＠	a1 + a2 + a3
2	2	b1	b2	b3		@ + @＋＠	b1 + b2 + b3
3	3	c1	c2	c3		@ + @＋＠	c1 + c2 + c3
Sheet3

Cell Formulas
Range	Formula
G1:G3	G1	=TEXTJOIN(" + ",,B1:D1)

Sorry for insufficient information.
The original text "@+@+@" for replacement may change to "@ and @ except @ " or any text content that carries "@". The target is to replace each "@" by the corresponding lookup value one-at-a-time in the same row.

 

[Joe4]

As awoohaw asked, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[smallxyz]

As awoohaw asked, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated. Thanks for reminder.