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, 2022 in Others by Kithuzzz
• 38,000 points
521 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, 2022 by narikkadan
• 63,600 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, 2022 in Others by narikkadan
• 63,600 points
3,592 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, 2022 in Others by narikkadan
• 63,600 points
1,153 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, 2022 in Others by narikkadan
• 63,600 points
1,000 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,000 points
661 views
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, 2022 in Others by Edureka
• 13,690 points
4,083 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, 2022 in Others by Edureka
• 13,690 points
2,004 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, 2022 in Others by Edureka
• 13,690 points
983 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,708 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, 2022 in Others by narikkadan
• 63,600 points
3,869 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, 2022 in Others by narikkadan
• 63,600 points
1,387 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