Forum Discussion

Copper Contributor

Aug 27, 2023

Solved

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.

HansVogelaar

MVP

Aug 27, 2023

Susie_P

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Susie_P

Copper Contributor

Aug 27, 2023

Thanks Hans - I have tried the suggestion from OliverScheurich and it worked.
Thanks for taking the time to reply to me though 🙂