Need to replace part of text in one cell based on value in another

[rpierre]

Hi,

I need help setting up a formula that pulls from text in other cells to create a customized message.

Context: parents are calling to set up appointments to meet with teachers for specific time slots. Once the appointment is set, Ms. John would select the student's name from a drop down menu (cells B3:C11) then send the parent a confirmation text. I'd like to have the text message auto-populated with the appointment information once a student's name is entered into cells B3 to C11.

For example, in the case of the parent of Leanna Smith having an appointment with Ms. John on Friday 11/20 at 1:30pm, the text in B13 will populate to read: "Meeting Confirmation: You are confirmed to meet with Leanna Smith's Reading Teacher, Ms. John, on Friday 11/20 at 1:30pm. Zoom link here: zoom.us/0000000000. Please be prompt so that later appointments aren't delayed. See you then!"

Appreciate anyone's help! Thanks in advance.

 

[ExcelGzh]

Formula for cell C13:

="Meeting confirmation: You are confirmed to meet with "&INDEX($B$1:$B$12,MATCH($A13,$A$1:$A$12,0),1)&"'s "&B$2&" at "&TEXT(TIMEVALUE(LEFT($C13,SEARCH("-",$C13)-1))+0.5,"h:mm am/pm")&". Zoom link here: "&[Enter cell reference for Zoom link]&". Please be prompt so that later appointments aren't delayed. See you then!"

This formula can then be copied to the other cells.

I have used INDEX/MATCH in case you don't have the latest version of Excel 365 which has a function called XLOOKUP. Let me know if you would like an XLOOKUP version.

 

[maabadi]

Welcome to MrExcel Message Board.

Change First text , Second Text and Last Text at formula to what you want.
Then Input formula to B13 then Drag it down & right:

=IF(B3="","","first text"&" "&B3&"Second Text"&" "&$B$1&" "&"on"&" "&B$2&" "&"at"&" "&LEFT($A3,FIND("-",$A3,1)-1)&"Last Text")

or

=IF(B3="","",""Meeting Confirmation: You are confirmed to meet with"&" "&B3&"'s Reading Teacher,"&" "&$B$1&" "&"on"&" "&B$2&" "&"at"&" "&LEFT($A3,FIND("-",$A3,1)-1)&". Zoom link here: zoom.us/0000000000. Please be prompt so that later appointments aren't delayed. See you then!")