Applying Proper Case to Strings

[Opv]

I have a worksheet within which users may enter words or phrases that include a variety of non-alpha characters. For example, boar/mix, boar-mix, Boar/mix or or any other delimiter and combination of words. I initially wrote a script which converted these entries (as they are entered) so that the sheet would reflect a standardized capitalization for each word in the name, i.e., Boar/Mix, Boar-Mix, etc.

My script works fine when users apply either "/" or "-" as the delimiter; however, one user has used a character which I can't seem to get my script to force the proper case. See, for example, the following:

Ok·cate·uce

I can't seem to identify which character is being for the delimiter. So, I decided to cycle through the characters one at a time and capture the character based on a predefined list of allowed values (all of the lower case and upper case letters) and it works through every step of the process up to the point that of achieving "Ok Cate Uce" however when I apply the last command to replace the spaces with the delimiter character and apply the changes to the applicable cell, Excel automatically changes it back to Ok·cate·uce

I can't figure out why that delimiter works differently from "/" and "-".

Any ideas?

[Leith Ross]

Hello Opv,

I don't think it is a Proofing (Auto Correct) setting. Did you protect the worksheet?

[Opv]

Hello Opv,

I don't think it is a Proofing (Auto Correct) setting. Did you protect the worksheet?

Thanks. The worksheet is not protected. As I said, I can get a script to perform as desired with other delimiters (such as "/" or "-"). It's just that weird centered dot (whatever it is) that is throwing a kink into things.

[Leith Ross]

Hello Opv,

You should post your code and if possible a copy of the workbook.

[Opv]

Hello Opv,

You should post your code and if possible a copy of the workbook.

Well, I copied my code to a new workbook, with a clean worksheet and pasted in the phrase with the problematic delimiter, created a module, pasted in my code, and it works perfectly in the new workbook. Before I trouble the forum further, let me do some trouble shooting on my original workbook. Perhaps there is something in one of my other blocks of code that is causing the problem. Will advise. Thanks.

[Leith Ross]

Hello Opv,

Hopefully it was just a corrupted workbook.

[snb]

Sub M_snb()
   MsgBox "Ok cate Use"
   MsgBox StrConv("ok cate use", 3)
   MsgBox Replace(StrConv(Replace("ok/cate/use", "/", " "), 3), " ", "/")
   MsgBox Replace(StrConv(Replace("ok-cate-use", "-", " "), 3), " ", "-")
   MsgBox Replace(StrConv(Replace("ok_cate_use", "_", " "), 3), " ", "_")
   MsgBox Replace(StrConv(Replace("Ok·cate·uce", Chr(182), " "), 3), " ", Chr(182))
End Sub

[SamT]

In Excel XP on WinXP:

MsgBox Replace(StrConv(Replace("Ok·cate·uce", Chr(182), " "), 3), " ", Chr(182))
'and
Cells(1, 4) = Replace(StrConv(Replace("Ok·cate·uce", Chr(182), " "), 3), " ", Chr(182))

Results = "Ok·cate·uce"

[snb]

You should upgrade to:

   MsgBox Replace(StrConv(Replace("ok·cate·uce", Chr(183), " "), 3), " ", Chr(183))

[Opv]

Thanks to everyone for the comments and suggestions. After realizing that my code worked when copied to a new workbook and clean worksheet, I went back and did some trouble shooting. The only thing I can figure is that something about my original workbook must have been corrupted. Regardless...problem solved. Thanks again!

[SamT]

Yep, That did it

MsgBox Replace(StrConv(Replace("ok·cate·uce", Chr(183), " "), 3), " ", Chr(183))