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,010 points
320 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,420 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,420 points
2,428 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,420 points
623 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,420 points
744 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,010 points
467 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,670 points
1,237 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,670 points
1,423 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,670 points
697 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
2,330 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,420 points
3,065 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,420 points
818 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