Sentence Case Question

[odie837]

Hi,
I have a column of data that I need to change from all capital letters into sentence case (not proper case). For example:

THIS IS WHAT I HAVE. THIS IS WHAT I HAVE.

...and this is what I want:

This is what I have. This is what I have.

I've looked online through this message board and there doesn't really seem like an easy way to do it (or even any way to do it). Manually I know I can copy the column of data into Word and have word change it into sentence case, and then repaste it back into my spreadsheet, but I would really like to write this into my macro so that it does this automatically.

Thanks! Any help is greatly apprechiated!!!


Odie

 

[Aladin Akyurek]

Try ASAP Utilities from http://asap-utilities.com.

 

[odie837]

Thanks, but I'm writting this macro for someone else and I'm not sure if they're going to be using the macro on the same computer, so I don't think a download would solve my problem.

Thanks a ton anyways!

 

[struf]

how about:

=lower(A1)



-------struf

 

[Smitty]

You can use Ivan Moala's Text Converter:
<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> TextConverter()
    <SPAN style="color:#007F00">'   Dimension Variables</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> RgText <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> oCell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> Ans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    Dim sCap <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, _
        lCap <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, _
        i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
Again:
    <SPAN style="color:#007F00">'   Request User Input for the Convert Procedure</SPAN>
    Ans = Application.InputBox("[L]owercase" & vbCr & "ppercase" & vbCr & _
            "entence" & vbCr & "[T]itles" & vbCr & "[C]apsSmall", _
            "Type in a Letter", Type:=2)
    <SPAN style="color:#00007F">If</SPAN> Ans = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> InStr(1, "LUSTC", UCase(Ans), vbTextCompare) = 0 <SPAN style="color:#00007F">Or</SPAN> Len(Ans) > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Again
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> NoText
    <SPAN style="color:#00007F">If</SPAN> Selection.Count = 1 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> RgText = Selection
    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> RgText = Selection.SpecialCells(xlCellTypeConstants, 2)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oCell <SPAN style="color:#00007F">In</SPAN> RgText
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(Ans)
            Case "L": oCell = LCase(oCell.Text)
            Case "U": oCell = UCase(oCell.Text)
            <SPAN style="color:#00007F">Case</SPAN> "S": oCell = U<SPAN style="color:#00007F">Case</SPAN>(Left(oCell.Text, 1)) & _
                LCase(Right(oCell.Text, Len(oCell.Text) - 1))
            Case "T": oCell = Application.WorksheetFunction.Proper(oCell.Text)
            Case "C"
                    lCap = oCell.Characters(1, 1).Font.Size
                    sCap = Int(lCap * 0.85)
                    <SPAN style="color:#007F00">'Small caps for everything.</SPAN>
                    oCell.Font.Size = sCap
                    oCell.Value = UCase(oCell.Text)
                    strTest = oCell.Value
                    <SPAN style="color:#007F00">'Large caps for 1st letter of words.</SPAN>
                    strTest = Application.Proper(strTest)
                    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Len(strTest)
                        <SPAN style="color:#00007F">If</SPAN> Mid(strTest, i, 1) = UCase(Mid(strTest, i, 1)) <SPAN style="color:#00007F">Then</SPAN>
                            oCell.Characters(i, 1).Font.Size = lCap
                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                    <SPAN style="color:#00007F">Next</SPAN> i
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">'   Error Handler</SPAN>
NoText:
MsgBox "No Text in your selection @ " & Selection.Address

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

 

[odie837]

I tried using Ivan Moala's Text Converter, and it works pretty well, but sometimes I have more than one sentence in a cell, and his text converter only capitalizes the very first word in the cell. Not anything that comes after a punctuation mark. But thanks for you help anyways!

 

[just_jon]

A daunting task, as you've not only got punctuation but Mr/Miss/Mrs/Ms as well as Dr/MD, etc ...

 

[odie837]

Yes, this is a huge pain in the butt. Basically is there any code that I could use to open up Microsoft Word, paste in the column that I need changed, then in Word change it to sentence Case, and from there copy and paste back into my original spreadsheet and close the Word document without saving it. I can figure out how to change it all to sentence case, but I need help opening Word, switching back and forth from applications, and closing word without saving the document.

Thanks!

 

[Ekim]

Odie,

Please post some sample data. I have a routine that that does sentence case in Excel, including multiple sentences in a cell. However, the routine doesn’t handle people’s names. Incidentally, MS Word’s sentence case doesn’t handle people’s names very well, for example, “MR AND MRS BLACK” is returned as “Mr and mrs. black”. If you put a period after Mr and Mrs, the return is “Mr. And mrs. Black”

With my routine, if you have strings formatted like the following:

THIS IS WHAT I HAVE. THIS IS WHAT I WANT. DO I? YES, I DO!
THIS IS WHAT I HAVE. I WANT THIS.

The macro will return:

This is what I have. This is what I want. Do I? Yes, I do!
This is what I have. I want this.

BTW, try formatting the first string with MS Word’s sentence case. You will not get the desired outcome. The point being, copying/pasting to Word may not be the answer.

Regards,

Mike

 

[odie837]

Here is some sample data: (each line represents one cell)

MAKE 2 FIXTURES TO SET CLEARANCE OF 3 ROUND CONNECTORS. SIMILAR TO 90-5065-01. OLD CHARGE NUMBER WAS 2N09-3136.

CREATE TOOLING NUMBER AND RELEASE UPDATED DRAWING PALOMAR 3500 UNIVERSAL SUBSTRATE FIXTURE

MAKE A FIXTURE TO HOLD FET DURING WAVE SOLDERING. USE 2 FARTHEST TOOLING HOLES ON PANEL AS GUIDE HOLES.

DESIGN AND FABRICATE A HOOD OR SHIELD FOR THE 25 DEGREE MIRROR ON THE CAMERA ALIGNMENT FIXTURE TO PREVENT MOVEMENT DURING ALIGNMENT.

REPAIR BROKEN SCREW ON TOOL

TOOL NEEDED TO MAINTAIN SET POSITION ON ANTI-BACKLASH GEARS DURING THE ASSEMBLY/ADJUSTMENT OF LASER BORESIGHT MECHANISM 105K864G01 (FORMERLY 820R181G01). SEE CHRIS CARR FOR DETAILS.


...I realise that Word's sentence case doesn't work perfectly, but going back through the data to check for names shouldn't be too big of a deal.

Thanks for your help Mike!
Odie