PROPER() Function Workaround where apostrphes occur

[kyjae]

Hi All,

Anyone know of a workaround or alternative function when PROPER() fails with apostrophes?

Simple example:

Type in cell A1 the words martin's car
Type in cell B1 =PROPER(A1)

Result shows as Martin'S Car

Obviously the S at the end of Martin should remain in lower case but PROPER() seems to fall over on this!

Any advice greatly appreciated.

Thanks

[duanzhuanming]

Hi,
try this:

=SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","excel")),"excel","'")

[kyjae]

Thank you!

Works perfectly. Would you take some time to explain how this formula works? Especially the "excel" bit as I've come across this before.

Thank you.

[FDibbins]

What they did was replace ' with the word excel - they could have chosen anything, as long as it would have been unlikely to exist in any of your text. They then applied the PROPER(), and then replaced excel with ' to get things back to where they were

[kyjae]

Thank you so much for this.

Another Excel snippet learned!

[FDibbins]

happy to help