Proper case problem

[ImranBhatti]

Hi

I want to proper case "ala'a" as "Ala'a" but I get "Ala'A"
I want to make first letter of every word if it is separated by space and not by a non alpha character

Thanks in advance
Imran Bhatti

[JohnTopley]

This ..???

=UPPER(LEFT(A1))&MID(A1,2,255)

[Special-K]

Thinking...

[ImranBhatti]

This ..???

=UPPER(LEFT(A1))&MID(A1,2,255)

This fails if there are other words
Like Ala'a hussain

[shukla.ankur281190]

Try

Please Login or Register  to view this content.

[than_gold]

Try this one:

Please Login or Register  to view this content.

[ImranBhatti]

This name is a variable.Can hard code the parts

[ImranBhatti]

@than

It also not helped

[than_gold]

@than

It also not helped

wondering, it's not working...which excel version you are using?

[than_gold]

give some more examples..

upper case.PNG

[Special-K]

Supply a REASONABLE amount of test data or you will end up with it not working.
One piece of data is not a reasonable amount.

[ImranBhatti]

@than
I need proper case means capitaling every words's first letter . Your formula not capitaliizng

[ImranBhatti]

Hi Forum signing off for two hours from the office will be online from home.

[JohnTopley]

Assuming words are delimited by blank

Please Login or Register  to view this content.

=Ufirst(A1)

[Haseeb Avarakkan]

Try something like,

Please Login or Register  to view this content.

Substitute ' with ß, use Proper. Then substitute ß with '

[ImranBhatti]

Sample attached. A column is original text while B column is desired

[Special-K]

Try something like,

Please Login or Register  to view this content.

Substitute ' with ß, use Proper. Then substitute ß with '

This was my first answer which I scrapped as the PROPER automatically changed the last a to uppercase regardless of what ASCII character I put in there.
Strangely this works using the Beta character you suggest.

[Special-K]

Sample attached. A column is original text while B column is desired

You have typos on B2 and B4.
Using Haseeb A's solution works though

[ImranBhatti]

Yes Special-K there were two typos.
Both the VBA way of John and Formula way of Haseeb are working now. Thanks all of you for the precious time.

[than_gold]

Thank you & add reputation please...

[ImranBhatti]

Yes I will add reputation despite they all are beyond repute.

And here I must attache the file with both the solution applied so that the researchers may have a ready made solution file on the web.