Using Excel Proper Function with exception Excel

Question

In essence, my Excel Spreadsheet has a number of strings that are organised as follows:

JOHN-MD-HOPKINS
REC-PW-RESIN

I would like to use the proper function but exclude the part of the string that is within the dashes (-).

The end result should look like the following:

John-MD-Hopkins
Rec-PW-Resin

Is there an excel formula that is capable of doing this?

Answer 1

To accomplish this, you might need to write your own VBA code that first determines whether the data contains two hyphens and, if so, only changes the first and last words to the correct case, leaving the middle word alone.

Paste the following into a module within Excel:

Function fProperCase(strData As String) As String
    Dim aData() As String
    aData() = Split(strData, "-")
    If UBound(aData) - LBound(aData) = 2 Then   '   has two hyphens in the original data
        fProperCase = StrConv(aData(LBound(aData)), vbProperCase) & "-" & aData(LBound(aData) + 1) & "-" & StrConv(aData(UBound(aData)), vbProperCase)
    Else    '    just do a normal string conversion to proper case
        fProperCase = StrConv(strData, vbProperCase)
    End If
End Function

When that happens, you can use this formula in your worksheet just like you would any built-in formula. For example, if "JOHN-MD-HOPKINS" is in cell A1, you can use this formula in another cell as follows:

=fProperCase(A1)

Which would display John-MD-Hopkins as required.