CODEWITHSUNDEEP
excelvba
gaus shaikh
gaus shaikh

Reputation: 1

Extract middle name with VBA based on Excel formula

I am using an Excel formula to extract the middle name of student.

The below formula is providing correct output. When I am trying to get the same output using VBA, it is showing error

Unable to get search property

=IF(LEN(I3)-LEN(SUBSTITUTE(I3,"-",""))=2,MID(I3, SEARCH("-",I3) + 1, SEARCH("-",I3,SEARCH("-",I3)+1) - SEARCH("-",I3) - 1),MID(I3,FIND("-",I3)+1,99))

Name in 'I' column - Gupta-Sarika-Ashok, I am getting output as 'Sarika' using above formula. This is not the case while using VBA

ws.Range("J2").Formula = WorksheetFunction.If(Len(I3) - len(WorksheetFunction.Substitute(I3, "-", "")) = 2, Mid(I3, WorksheetFunction.Search("-", I3) + 1, WorksheetFunction.Search("-", I3, WorksheetFunction.Search("-", I3) + 1) - WorksheetFunction.Search("-", I3) - 1), VBA.Mid(I3, WorksheetFunction.Find("-", I3) + 1, 99))

Upvotes: 0

Views: 187

Answers (1)

Toni
Toni

Reputation: 1585

Try this:

ws.Range("J2").Formula = "=IF(LEN(I3)-LEN(SUBSTITUTE(I3,""-"",""""))=2,MID(I3, SEARCH(""-"",I3) + 1, SEARCH(""-"",I3,SEARCH(""-"",I3)+1) - SEARCH(""-"",I3) - 1),MID(I3,FIND(""-"",I3)+1,99))"

Upvotes: 2

Related Questions