Proper Case

[rakul_rakul]

Hi All,

I have some values in cell A1. Lets say "Saravanan Rajendran, Rajendran Palanisamy, Sarojini Rajendran, Vijayaraja Rajendran"

I am trying to change this to "Saravanan rajendran, Rajendran palanisamy, Sarojini rajendran, Vijayaraja rajendran"

Where in, only the first letter of a sentence should start up with capital letter and the rest of the sentence should be in lower case. After a comma, again the first letter of a sentence should start with capital letter and the rest of the sentence should have small letters. I am trying to use multiple formulas but I couldn't bring this way.

Looking for your valuble input.

Regards,

Rakul

[Excelski]

Try =CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))

Sorry this doesn't work, just read the question properly

[rakul_rakul]

Hi Excelski,

The given formula does not work.

Rakul

[FlameRetired]

Rakul,

This solution is a bit involved. The last step (re-concatenation) requires VBA ... a user defined function. This one can be found here.http://www.excelforum.com/tips-and-t...ml#post3096647

This is the code.

Please Login or Register  to view this content.

If you are not familiar with how to install VBA code:


How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the code into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

This also uses a helper cell in B1 that calculates the number of items between the ", ".

Please Login or Register  to view this content.

The final formula is array entered in C1. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Row\Col	A	B	C
1	Saravanan Rajendran, Rajendran Palanisamy, Sarojini Rajendran, Vijayaraja Rajendran	4	Saravanan rajendran, RajendRan palanisamy, Sarojini rajendran, Vijayaraja rajendran