PDA

View Full Version : [SOLVED] Applying Proper Case to Strings



Opv
05-03-2017, 07:58 AM
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
05-03-2017, 10:54 AM
Hello Opv,

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

Opv
05-03-2017, 11:05 AM
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
05-03-2017, 11:19 AM
Hello Opv,

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

Opv
05-03-2017, 11:39 AM
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
05-03-2017, 11:44 AM
Hello Opv,

Hopefully it was just a corrupted workbook.

snb
05-03-2017, 11:46 AM
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
05-03-2017, 01:06 PM
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
05-03-2017, 02:16 PM
You should upgrade to::whistle:


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

Opv
05-03-2017, 02:18 PM
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
05-03-2017, 09:05 PM
Yep, That did it

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