Excel vba coding to replace each string to different text or nest formula?

[AS12Excel]

Hello,
I'm trying to replace 100 cells in a column that has different values. The result will be in another column that identify the first letter of that cell and replace with one the 3 names in all selected rows (Mary, Thomas, Hilton). I believe a VBA code would be easier than a nest formula itself?

per example:

Existent cells:
M0779
T1524
H0084

Desired result
Mary
Thomas
Hilton

Thanks for helping!

 

[Peter_SSs]

Assuming original data in column A & results in column B, try this in a copy of your workbook.

Sub ReplaceWithName()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(#="""","""",iferror(choose(search(left(#,1),""MTH""),""Mary"",""Thomas"",""Hilton""),#))", "#", .Address))
  End With
End Sub

The worksheet formula equivalent probably isn't as bad as you thought:

Excel Workbook
	A	B
1	Data	Result
2	M0779	Mary
3
4	T1524	Thomas
5	H0084	Hilton
6	xczxc	xczxc
Replace