PROPER text in excel cell 1

[JuztinMedia]

Here is the formula I'm using:

=PROPER(A35)

The problem is, if the sentence in A35 says this "IT'S SUNNY" .. it returns it like this "It'S Sunny". Anytime there is a apostrephe or comma, it makes the next letter in CAPS.. how can I make it do "proper" sentence formatting? Like... "It's Sunny" ?

HELP.. please! Thanks Thanks!
-JusTin

 

[SkipVought]

Ha!

Microsoft'S own example has a contraction that shows 'S

Looks like yer stuck! Skip,
SkipAndMary1017@mindspring.com

 

[DaleWatson123321]

The LONG way... submit a complaint to Microsoft, and wait for the NEXT, NEXT, NEXT release.

A shorter way...

1) Select ALL the cells referenced by the formulas using the PROPER function. (If your sheet ONLY contains these PROPER function formulas, then select the entire worksheet).

2) Use the &quot;Replace&quot; window - Hold down <Control> and hit &quot;H&quot;.

3) Under &quot;Find what:&quot;, enter the apostrophe: '

4) Under &quot;Replace with:&quot;, enter a character that is not going to be found anywhere on your selected range. An easy way of creating such a character is to:
Hold down the <Alt> key, and hit 134 (on the number pad). This will produce the character: å

5) Click &quot;Replace All&quot; (or <Alt> A)

6) Select the formula cells, and convert the formulas to values... Copy, the Paste Special - Values

7) Select the text cells and the formula cells (now values).

8) Use the &quot;Replace&quot; window - <Control> H.

9) Under &quot;Find what:&quot;, replace the apostrophe with the &quot;å&quot; character (Alt 134)

10) Under &quot;Replace with:&quot;, replace the &quot;å&quot; character with the apostrophe.

11) Click &quot;Replace All&quot; (or <Alt> A)

I hope this can be a useful alternative. Please advise as how it works out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[mscallisto]

=PROPER(MID(A1,1,SEARCH(&quot;'&quot;,A1,1))) & LOWER(MID(A1,SEARCH(&quot;'&quot;,A1,1)+1,1))

Of course for cells with no apostrophy you wi have to proceed this with IF(Iserror....

 

[mscallisto]

Actually I believe we are all wrong about the concept of Proper()

Proper() I believe, was designed to be used primarily for names thus the Capitalization after the quote. e.g. D'Angelo, D'Amico etc.

One can do as I did above (which by the way isn't exactly correct), to use as Proper() in general verbage. But you can't have it both ways. If you use it as I did you must be content with &quot;It's Sunny&quot; or &quot;Anthony D'amico&quot;.

Hope this makes sence and I'm working on correcting the above code, probably taking Dale's advice and using the substitute() function.

 

[mscallisto]

Actually I believe we're all wrong about the concept of the Proper()function.

Proper() I believe, was designed to be used primarily for names thus the capitalization after the quote. e.g. D'Angelo, D'Amico etc.

To use the proper() function in general sentence structure one can do as I did above (which by the way isn't exactly correct), but you can't have it both ways.

If you use it as I did you must be content with &quot;It's Sunny&quot; or &quot;Anthony D'amico&quot;.

Hope this makes sence and I'm working on correcting the above code, probably taking Dale's advice and using the substitute() function.

Much Luck

 

[xlbo]

I believe mscallisto is correct - from xl help:
&quot;Proper&quot; function
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
Star for mscallisto for correcting my views on what PROPER does Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[RobBroekhuis]

Of course it would be relatively straightforward to write a custom function to arrive at any capitalization scheme imaginable, e.g. one that capitalizes after an apostrophe except if it's an S or T followed by a non-letter.
This would result in
D'Amico
It's Sunny
He Wouldn't Play

Rob

 

[mscallisto]

One would still have to tend with I'm, I'll and so on.

What I simply wanted to stress is that there is no &quot;one cure&quot; algorythm when one wishes to Proper() both names and sentences at the same time.

Still I agree Rob, you can custom your code if you know your data.

 

[RobBroekhuis]

The English language, gotta love it.
Rob