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

rpierre

New Member
Joined
Nov 9, 2020
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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.
Screen Shot 2020-11-09 at 2.38.12 PM.png
 

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
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,925
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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!")
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top