Excel UDF with IF

[Mikeyabosbht]

Hi Guys,


I have created two separate UDF in VBA that parse the "Dear" and "Your / To" from a block of text to leave just the forename and surname. Unfortunately, they are separate.

I am looking to create one UDF with IF ELSE so it returns the forename and surname when it finds either "To" or "Your" based on the one custom UDF.

Is that possible?

[snb]

Function F_snb(c00)
F_snb = Replace(Split(c00, vbCrLf)(0), "Dear ", "")
End Function

Instead of 10 lines of code.

[arnelgp]

i made also 2 udf:


Public Function getEmployeeName(ByVal sText As String)
Dim iStart As Integer, iEnd As Integer
Dim vRet As Variant
iStart = InStr(1, sText, "Dear")
If iStart Then
iEnd = InStr(iStart, sText, vbCrLf)
If iEnd Then
vRet = Trim$(Mid$(sText, iStart + Len("Dear"), iEnd - 1))
End If
End If
getEmployeeName = vRet
End Function




Public Function getEmployerName(ByVal sText As String)
Dim s As String, v
With CreateObject("vbscript.regexp")
.MultiLine = True
.Global = True
.ignorecase = True
.Pattern = "[a-z',]+\s+[a-z']+\s+\(([0-9]+|[0-9]+\ +[a-z]+)\)"
Set v = .Execute(sText)
For Each var In v
s = var.Value
s = Trim$(Left$(s, InStrRev(s, "(") - 1))
Exit For
Next
End With
If InStr(s, ",") Then
v = Split(s, ",")
s = Trim$(v(1)) & " " & Trim$(v(0))
End If
getEmployerName = s
End Function




on Column D, you add a formula on Row 2 (copy it down the other rows):

="employee: "&getEmployeeName(D2)&", employer: "&getEmployerName(D2)

[snb]

@arnel


I am looking to create one UDF

[arnelgp]

i know, but the other (not the "Dear" part), how and which part on your Split()?

[snb]

Test it, please.

[p45cal]

I'm guessing the OP isn't looking for the employee name but the full name of the adressee.
snb's assumes the name is by itself on the first line and strips the 'Dear ' from it. Fair enough assumptions given the examples.
Here's another. It looks for the first instance of 'Dear ', then for the positions of the first instances of 'To' and 'Your' after that, then takes the text between 'Dear ' and the closest of To and Your, then trims out extra spaces. It's not always going to work (when there's no 'Dear ' or neither of 'To'/'From' present):
Function GetName(cll)
a = InStr(UCase(cll), "DEAR ")
b1 = InStr(a, UCase(cll), "TO")
If b1 = 0 Then b1 = Empty
b2 = InStr(a, UCase(cll), "YOUR")
If b2 = 0 Then b2 = Empty
GetName = Application.Trim(Mid(cll, a + 5, (Application.Small(Array(b1, b2), 1) - a - 5)))
End Function

[snb]

The example contains:

1 instance containing 'Dear"
1 instance not containing "Dear"
1 instance containing "Your"
1 instance containing "To"

So all 4 posibiliteis that has been described in the question do occur.
The Macros provided do not suggest any other.
So if the TS asserts the two UDFs provide the required result (name and surname) There is no reason to assume the provided example isn't representative.
In the posted example my UDF returns the name and surname (and does so in the not provided combinations).

[p45cal]

You could try a formula:
=TRIM(MID(D2,SEARCH("Dear ",D2)+5,MIN(IFERROR(SEARCH({"To","Your"},D2),""))-5-SEARCH("Dear ",D2)))

[Paul_Hossler]

UDF




Option Explicit


Function GetTheNames(s As String) As String
Dim v As Variant
Dim s1 As String

v = Split(Trim(s), Chr(10))

s1 = Trim(v(0))

s1 = Trim(Replace(s1, "Dear", vbNullString, 1, 1, vbTextCompare))
s1 = Trim(Replace(s1, "Your", vbNullString, 1, 1, vbTextCompare))
GetTheNames = Trim(Replace(s1, "Dear", vbNullString, 1, 1, vbTextCompare))
End Function

[arnelgp]

it's not Clear which "person" is being addressed in the "Your".
there are 3 persons in the letter:

1. the person in the "Dear" part (employee)
2. WECIL Payroll (representative)
3. the employer (on the bottom part, signature, far right from the "representative")

what my code is looking is point 1 (employee) and point 3 (employer).