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

#### rpierre

##### New Member
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.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### ExcelGzh

##### Board Regular
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.

##### Well-known Member
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:
Excel Formula:
``=IF(B3="","","first text"&" "&B3&"Second Text"&" "&\$B\$1&" "&"on"&" "&B\$2&" "&"at"&" "&LEFT(\$A3,FIND("-",\$A3,1)-1)&"Last Text")``

or

Excel Formula:
``=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!")``

Replies
2
Views
226
Replies
2
Views
129
Replies
6
Views
144
Replies
3
Views
93
Replies
1
Views
160

1,132,914
Messages
5,655,937
Members
418,253
Latest member
TheJackal26

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back