VBA Code or formula to replace "Employee" with the second name of a different cell.

[Vacheron23]

Hi Everyone, I have an annual assessment form that collects data from certain fields and based on the grading given to those fields, specific comments will be generated on column "AH" for each respective employee. All those comments state "Employee" at the beginning of each sentence. What I would like to do is to change the "Employee" with the second name from column B for each entry so that the comments will be more personalized. I had attached a photo of the file in question (unable to upload document). Any help is appreciated. Thank you very much!

 

[dermie_72]

you could use a formula to substitute the text:

=SUBSTITUTE(AH2,"Employee",TEXTAFTER(B2," "))

Then Value out the column and paste the values over the original text.

 

[PeteWright]

In order to provide you with the right formula or code I'll need a little more information.

Where is your data (the text for column AH) stored?

Meanwhile here's a suggestion:

MrExcel_2024-11.xlsm
	A	B	C	D
1	ID	Name	Grade	Comment
2	10000001	DOE John	0	John is OK
3	10000002	DOE Jane	1	Jane is a very good boi
4	10000003	DOE Jean	2	Jean is a good boi
5	10000004	DOE Jack	3	Jack is a bad boi
6
7	Samples
8	Grade	Comment
9	0	is OK
10	1	is a very good boi
11	2	is a good boi
12	3	is a bad boi
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	= TEXTAFTER(B2, " ", 1, 1, 0, "Employee") & " " & XLOOKUP(C2, $A$9:$A$12, $B$9:$B$12, 0,0,1)

 

[HighAndWilder]

Hi Everyone, I have an annual assessment form that collects data from certain fields and based on the grading given to those fields, specific comments will be generated on column "AH" for each respective employee. All those comments state "Employee" at the beginning of each sentence. What I would like to do is to change the "Employee" with the second name from column B for each entry so that the comments will be more personalized. I had attached a photo of the file in question (unable to upload document). Any help is appreciated. Thank you very much!

Does this work for you?

1. Select the cell(s) with formulas and press Ctrl + C to copy them.
2. Select the same range.
3. Press Excel's paste values shortcut: Ctrl + Alt + V, then V.
4. Press Enter

Extract the word which carries the specific text.xlsm
	A	B																																AH	AI	AJ
1		Full Name																																Comment
2		Smith John																																Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog,		John the quick brown fox jumps over the lazy dog, John the quick brown fox jumps over the lazy dog, John the quick brown fox jumps over the lazy dog,
3		Jones Harry																																Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog,		Harry the quick brown fox jumps over the lazy dog, Harry the quick brown fox jumps over the lazy dog, Harry the quick brown fox jumps over the lazy dog,
4		Grayson Jane																																Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog,		Jane the quick brown fox jumps over the lazy dog, Jane the quick brown fox jumps over the lazy dog, Jane the quick brown fox jumps over the lazy dog,
Employee

Cell Formulas
Range	Formula
AJ2:AJ4	AJ2	=SUBSTITUTE(AH2,"Employee",INDEX(TEXTSPLIT(B2," "),2))

 

[Vacheron23]

This is exactly what I need, thanks a lot!

 

[Vacheron23]

you could use a formula to substitute the text:

=SUBSTITUTE(AH2,"Employee",TEXTAFTER(B2," "))

Then Value out the column and paste the values over the original text.

This is exactly what I need, thanks a lot!

 

[Vacheron23]

Does this work for you?

1. Select the cell(s) with formulas and press Ctrl + C to copy them.
2. Select the same range.
3. Press Excel's paste values shortcut: Ctrl + Alt + V, then V.
4. Press Enter

Extract the word which carries the specific text.xlsm
	A	B																																AH	AI	AJ
1		Full Name																																Comment
2		Smith John																																Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog,		John the quick brown fox jumps over the lazy dog, John the quick brown fox jumps over the lazy dog, John the quick brown fox jumps over the lazy dog,
3		Jones Harry																																Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog,		Harry the quick brown fox jumps over the lazy dog, Harry the quick brown fox jumps over the lazy dog, Harry the quick brown fox jumps over the lazy dog,
4		Grayson Jane																																Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog, Employee the quick brown fox jumps over the lazy dog,		Jane the quick brown fox jumps over the lazy dog, Jane the quick brown fox jumps over the lazy dog, Jane the quick brown fox jumps over the lazy dog,
Employee

Cell Formulas
Range	Formula
AJ2:AJ4	AJ2	=SUBSTITUTE(AH2,"Employee",INDEX(TEXTSPLIT(B2," "),2))

This is exactly what I need, thanks a lot!

 

[Peter_SSs]

If you did not want to introduce another another column to your formal table, and given you mentioned the possibility of vba, you could try this with a copy of your workbook (after checking/adjusting the table name). This will replace the original data in the 'COMMENT' column. I have assumed the table will be on the active sheet when the code is run.

Sub Employee_Name()
  With Range("Table1[COMMENT]")
    .Value = Evaluate("substitute(" & .Address & ",""Employee"",textafter(" & Range("Table1[FULL NAME]").Address & ","" ""))")
  End With
End Sub