Solved: Solved - Help to build a function to detect Captial Letters in a Cell

[psionic]

Hi again :hi: with another problem I am stumbling here. :banghead:

I wanted to build a function that returns "Y" if there at captial letters in the provided text and length of text while I am looping a list of cells by rows that checks the string of names. (no problem there). :whistle:

The trouble is there are spaces and dashes which confuses the best way to find out if I need to return a Y. :motz2:

I am not interested in the first letter of the received string as it is obviously always captial or lower case.

If there are spaces in the name, the captial letter after the space doesn't count at all, this represents like Sentence Case in Word. Not interested in that. If there are Captial letters after a dash, this captial letter doesn't count either.

Other conditions, (you love these! :goofball: DR, Dr, DR., dr., Prof., I am not interested in them or needs to be ignored/skipped). The rest of the text if in the ASCII Range of 69 - 90 that is, A-Z, I'm interested in being flagged for "Y". Any numbers included in the name can return a X instead of Y so I know there are numbers in them.

The purpose is to find mistakes made by other people in the database when exported to Excel so I run a VBA macro against the data to resolve them.

Here is an example to understand the nature.


A B
1 Cat, DR G
2 DOG23, DR H X
3 ALL-GIATOR, Prof G Y
4 Monkey, Dr. P
5 George, A
6 HOWARD, J Y
7 Von Der Such32, E X
8 Von-Der Such, E
9 VON-DEA, PEACH Y
10 All-Giator, Prof G

So I got Rows 2,3,6,7,9 to work on.


I have written my code that calls the function with something like this.


cell.Offset(, 15).Value = Check_Capital_Letters(cell.Value, length(cell.Value))

Function Check_Captial_Letter (text as String, num as Integer)
'Check the text .....

Check_Captial_Letters = "Y"

End Function



Let me know if I need to clarify.

Thanks again and warm regards,
Psionic

[mikerickson]

If there are spaces in the name, the captial letter after the space doesn't count at all, this represents like Sentence Case in Word. Not interested in that. If there are Captial letters after a dash, this captial letter doesn't count either.

7 Von Der Such32, E X
8 Von-Der Such, E

So I got Rows 2,3,6,7,9 To work on
why does 7 get flagged, while 8 doesn't?

[mikerickson]

Other than the 7,8 issue, this UDF will return True if the inputString has a bad captial.
Unless you are working on a Mac or are using before Excel 2003, the Application.Substitute can be replaced with the function Replace.

Function yesCaps(inputString As String) As Boolean
Dim i As Long
inputString = Application.Substitute(inputString, "DR", "a")
inputString = Application.Substitute(inputString, "Dr", "a")
inputString = Application.Substitute(inputString, "Dr.", "a")
inputString = Application.Substitute(inputString, "Prof.", "a")
For i = 65 To Asc("Z")
inputString = Application.Substitute(inputString, " " & Chr(i), "a")
inputString = Application.Substitute(inputString, "-" & Chr(i), "a")
Next i
yesCaps = (inputString Like "?*[A-Z]*")
End Function

[psionic]

Hi mikerickson,

I gave this code a run and I must say I thought it would be solve it programmically with a number of if's and select case's but this is different like a style of art!

It works very well. :friends:

Thanks and warm regards,
Psionic.

P.S. I can add some more constraints like atmosphere's as well.

[mikerickson]

You're welcome.