Using Excel Proper Function with exception Excel

0 votes

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?

Oct 21 in Others by Kithuzzz
• 20,660 points
33 views

1 answer to this question.

0 votes

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.

answered Oct 22 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
56 views
0 votes
1 answer

Excel OFFSET function with a dynamic row argument

The answer is to make "anchor cells" ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
70 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
61 views
0 votes
0 answers
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
137 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
230 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
101 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
68 views
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
175 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
110 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP