Substituting text (based on a key) in a cell already containing the 'concatenate' formula

Hi there, I am a teacher and am trying to personalise a paragraph of text that has already been pre-populated, and concatenated from statements pulled from another sheet. Do I need to tell excel to ...

Formulas and Functions

OliverScheurich
Aug 27, 2023
Susie_P
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
CONCATENATE('Maths data'!F8," ",'Maths data'!I8," ",'Maths data'!R8," ",'Maths data'!AA8," ",'Maths data'!AJ8," ",'Maths data'!AS8," ",'Maths data'!BB8," ",'Maths data'!BK8," ",'Maths data'!BT8," ",'Maths data'!CC8," ",'Maths data'!CL8),
"#N",B3),
"#E",IF(C3="f","She","He")),
"#h",IF(C3="f","her","his")),
"#H",IF(C3="f","Her","His"))

You can apply the SUBSTITUTE function.

OliverScheurich

Gold Contributor

Aug 27, 2023

Susie_P

=SUBSTITUTE(

SUBSTITUTE(

CONCATENATE('Maths data'!F8," ",'Maths data'!I8," ",'Maths data'!R8," ",'Maths data'!AA8," ",'Maths data'!AJ8," ",'Maths data'!AS8," ",'Maths data'!BB8," ",'Maths data'!BK8," ",'Maths data'!BT8," ",'Maths data'!CC8," ",'Maths data'!CL8),

"#N",B3),

"#E",IF(C3="f","She","He")),

"#h",IF(C3="f","her","his")),

"#H",IF(C3="f","Her","His"))

You can apply the SUBSTITUTE function.

Susie_P
Copper Contributor
Aug 27, 2023
Brilliant - this works, thank you so much!

Forum Discussion

Substituting text (based on a key) in a cell already containing the 'concatenate' formula

Resources