If the first letter of each substring is capitalized, then highlight cell

[therealdees]

Hello everyone,

I have this code I edited a while back, that I use every week to find mistypes in columns containing address registries.

The logic is: Most addresses are input through autocompletation by typing the ZIP code, and in this case, the only field that will need manual typing is the number of the building in its street. When autocompletation is used, every field containing text (eg. street name, city name) is typed either A-Z (RIO DE JANEIRO) or A-z (Rio De Janeiro).

Considering that, I know that every a-z string is a manual entry, so I use that to highlight these strings. The problem is I can't identify the A-z like strings. Does anyone have an idea of how I could achieve that?

The part highlighted in red is the piece I added, but I guess it will only work with A-Z or a-z, not A-z, how I thought.

Please Login or Register  to view this content.

[therealdees]

I thought maybe using the PROPER function like this:

Please Login or Register  to view this content.

it doesn't work, but maybe the logic is right

[jolivanes]

Capitals are characters 65 - 90 and small letters are 97 - 122.
You could check the character number of the first character and if it is >64 And < 91, it is capitalized.

Please Login or Register  to view this content.

Here is another snippet to adapt if you want to.

Please Login or Register  to view this content.

[Vraag en antwoord]

Isn't it easy with conditional formatting?

=CODE(LEFT(A2,1))>90 

[jolivanes]

@ Vraag en antwoord
What happens when you get into cities in your area like 's Gravenhage, 's Hertogenbosch.
I don't know if other countries have the same.
Mind you, that is a topic on it's own because officially it starts with a small letter, just like the country name, the Netherlands. (if I remember this right)

BTW, congratulations with Max at Paul Ricard. Fantastic and well deserved. I hope he has the equipment to continue like this.

[Vraag en antwoord]

@jolivanes,
Writing like Den Haag and Den Bosch? The conditional formatting can of course also be adjusted to some more conditions.

Unfortunately only saw the summary of the F1 race but a nice season so far. Finally some competition again.

How is it going in Canada. Or are we hijacking the thread a bit?

[Artik]

Perhaps this path can be followed:

Please Login or Register  to view this content.

Artik

[jolivanes]

@ Vraag en antwoord.
Sent a PM

[therealdees]

Hello everyone,

Thank you so much for the replies, I will try the provided codes right now

[therealdees]

Isn't it easy with conditional formatting?

=CODE(LEFT(A2,1))>90 

Although it's a good option, it won't help me that much because I run the codes over a new workbook sample every time. I got like 5-8 codes that I run and will flag some cells holding specific situations, then I take the result and merge it with other file that I use for reporting. Anyway, thank you for the tip

[therealdees]

Perhaps this path can be followed:

Please Login or Register  to view this content.

Artik

Hey, the code makes sense to me, but it didn't work as expected. I'm attaching a sample holding all different situations that'd be flagged. The "all lowercase" and "all uppercase" did work, but the "capitalize first letter" was flagged as yellow instead of green (so the yellow condition would flag every other condition than those listed before?).

Also, by using the vbProperCase, it'll consider the first letter of each substring as a capital, right? Or only the first letter of the string?

And as a last doubt, will special characters interfer the way the code will work? I added to the sample some situations where the string hold numbers and/or some other special characters.


PS: I just realized there's a problem with the logic when dealing with prepositions words (ex. "of", "from"). Some of the [A-z] like (correct and input by autocompletation) may hold prepositions that will be a-z like. For instance, instead of Rio De Janeiro, it'll be Rio de Janeiro. Assuming the PROPER FUNCTION logic will consider every substring, I think it won't work. Maybe a solution would be checking only for the first and last substring of the text, as there will never be a preposition without a following word.

[Vraag en antwoord]

Seems like a bad motivation not to use it. Apparently you have no knowledge of VBA?
Post an example file and I'll show you that it can be done with a few lines of code.

[therealdees]

Capitals are characters 65 - 90 and small letters are 97 - 122.
You could check the character number of the first character and if it is >64 And < 91, it is capitalized.

Please Login or Register  to view this content.

Here is another snippet to adapt if you want to.

Please Login or Register  to view this content.

This might be the solution using LEFT and Ucase. I will try to come up with something right now

[therealdees]

Seems like a bad motivation not to use it. Apparently you have no knowledge of VBA?
Post an example file and I'll show you that it can be done with a few lines of code.

Yes, I started with excel at all only in january. I'm still learning.

I assume conditional formatting will be set through as a new rule, not VBA, right? Wouldn't it be faster to run different codes into the sheet code page? Besides, conditional formatting will be updated real time? If so, it might conflict with other codes I run later.

I added a sample in the post above, see if it's good for this situation, or let me know if not and I will provide you one as you need.

[Vraag en antwoord]

Why quote every message? Why do you post a file without code? Why do it with VBA if you don't show the rest of the process?

[therealdees]

Why quote every message? Why do you post a file without code? Why do it with VBA if you don't show the rest of the process?

#1 To identify who I'm replying to

#2 Because there is no code, the purpose of the thread is helping me out to build one, but don't feel obligated

#3 Because VBA has saved me lots of time and effort in almost every task I do, and I can't share the whole file because it's CRM data, containing sensitive data. This is not the only flagging conditions I perform, so other codes might conflict with conditional formatting (as far as my knowledge goes) as its performed in real-time.

[Artik]

In the example works OK. We'll see how in life.

Please Login or Register  to view this content.

Artik

[therealdees]

Thank you very much!!!

Will try it right now and give you a feedback

[therealdees]

Hey, it did work for most cases, but there're some similar situations that I'm not sure why its being flagged differently. I'm building a sample sheet holding the cases there could be, but just for knowledge in the meantime the most weird was the following:


Both these cases were flagged orange instead of green (which is weird because in the sample I provided it did work perfectly with similar cases)

Condomínio Parque do Gama
Condomínio Mini Chácaras do Lago Sul
Avenida do Exército
Rua Luisa Pereira Gonçalves


I also forgot to mention that in some cases the autocompleted string (which will always be correct) may have some special characters like ( ) - and numbers, but I can deal with that later (probably by pointing out green every occurence of those). Unless it's something simple to add and wouldn't take much of your time.

Any idea why those cases above didn't work as expected? Maybe because of special characters like "ç" and accentuation?

[therealdees]

One more info that might be useful: every word that could come as a-z will be a preposition and I belive that in 99% of every case they will be max 3 lenght: do, da, dos, das, de (if there's any extra cases they'd be the exception and I can deal with them later)

Maybe if we point out that those 5 prepositions should be a-z it'd solve the problem, while every other word should be A-z, not matter if first or last word

[therealdees]

I attached a sample holding different cases that either worked correctly or went wrong + some special cases that I just noticed: these special cases will always have "lado" or "fim" (but they are also a very exception, so don't mind them too much if you think they'd be a bigger problem to solve, I can deal with them manually later or try to include them in the code)

[Artik]

The next version solves most of the cases described above. You can multiply other cases, but be aware that we will never solve all of them.
Please also note that code running on an already colored range may skip some cells (red and orange).

Please Login or Register  to view this content.

Artik

[Vraag en antwoord]

I haven't seen a response to #15 yet. Again you place an .xlsx. It cannot contain any code.

[jindon]

Condomínio Parque do Gama
Condomínio Mini Chácaras do Lago Sul
Avenida do Exército
Rua Luisa Pereira Gonçalves

One more info that might be useful: every word that could come as a-z will be a preposition and I belive that in 99% of every case they will be max 3 lenght: do, da, dos, das, de (if there's any extra cases they'd be the exception and I can deal with them later)

Maybe if we point out that those 5 prepositions should be a-z it'd solve the problem, while every other word should be A-z, not matter if first or last word

Please Login or Register  to view this content.

[therealdees]

Hello again jindon!

Thanks for the response, I will try it right now

[therealdees]

The next version solves most of the cases described above. You can multiply other cases, but be aware that we will never solve all of them.
Please also note that code running on an already colored range may skip some cells (red and orange).

Artik

Trying this right now too! Thanks

[therealdees]

@Artik

I'm not sure if I'm doing something wrong, but I'm getting a Type Mismatch error. I googled it and it says its a variable dimensioning problem. I tried running the code in the actual workbook and the last sample I attached, both without success.

Any idea?

PS: I tried running the code directly into the sheet code page and also assigning it to a button, in both cases I get the same error.

[therealdees]

@Jindon

I wasn't sure how to use your code as it's a function, but based on Artik last solution, I followed the same logic as it also contains 2 functions that I assume to complement the first one. I added it to the bottom of Artiks first code, but nothing happens when I click, no error or nothing. When I try to run the code without adding the complement, it runs. Any idea why?

[jindon]

First of all, is it working?

[therealdees]

If that's how I was supposed to use it, no, nothing happens

I added it to the bottom or artik first code, is that correct?

[jindon]

I'm talking about the results in my attachment.
Again the result in col.C True/False is what you expected or not.

This is the first step.

[therealdees]

Oh I'm sorry, I didn't see the attachment

The true/false statements are correct for the strings, but for the task I perform I need to flag the cells with colors so I can use that for a future proccess, so I think boolean wouldnt help me out much


EDIT: Maybe we could use the file in post #22 as the base sample. I added there some different cases that Artik code couldn't deal with. Anyway, these special cases containing "até" or "ao lado" are very less frequent and if it's too much I can deal with them manually. If I can flag the A-z cases (considering "do", "da", "dos", "das" will always be a-z like) it'd be a great help already

[jindon]

I need it to be correct, otherwise no point in continuing.

And DO NOT mix up with someone else' code, unless you are instructed to do so.

Now apply the function to Conditional formatting like attached.

[therealdees]

Yes! These cases are correct. The only problem is I belive conditional formatting will conflict when I run other codes to flag other things later, because it updates in real time. There are other flagging proccesses I run to identify other things that will hierarchially attribute some other colors (by hierarchially I don't mean it's automatic, it's just a different part of the proccess that might overrun a previous color if there's certain condition found within the string)

[jindon]

Then add this to the module

Please Login or Register  to view this content.

[therealdees]

It worked!!! Excluding the exceptions I mentioned, which shouldn't be a problem as they're much less frequent!

Thank you very much Jindon, and thanks for the patience, I'm learning everyday, but sometimes this still like chinese to me

<3

[therealdees]

I'll set the thread as solved, but @Artik, in case you're still interested, I'd love to try your code aswell, as it seems you tried to include these exception cases mentioned.

Thank you all!!!

[jindon]

If you upload a workbook with all the variations of the string and the result that you want, I will look at it and see if I can help.

[therealdees]

I will do it right now. Let me just clean some of the sensitive data

[therealdees]

Hey, I took only the special cases I mentioned, as the rest worked perfectly (as far I could see so far). The file I'm working with has about 100k rows, and these special cases I found don't sum up to 100 rows. I think I might be taking much of your time for something minimal that I could do manually, but I attached the file in case you're interested anyway.

[therealdees]

Can you explain me the red highlighted line? And how did you match the RGB colors? I forgot to mention that sometimes I might run the code over some cells that have already been flagged, and it may overrun them improperly. I was going to add an IF RGB(255, 204, 153) Or RGB(255, 199, 206) then do nothing before the line highlighted in blue, but I'm not sure if it'd work since you didn't use RGB (I guess). Any idea?

Please Login or Register  to view this content.

[therealdees]

I tried editing like the following, but still it overruns other flagged cells

Please Login or Register  to view this content.

[jindon]

Try change the function to

Please Login or Register  to view this content.

Only the row66
Avenida FlorianópolisParque Residencial Karla/Jardim Santa Rosa - até 924/925
This is not possible to evaluate as true.

[Artik]

@Artik

I'm not sure if I'm doing something wrong, but I'm getting a Type Mismatch error.

Aj! I wrongly corrected a piece of code without checking it.
It should be better now, but not perfect.
I am aware that RegEx offers much more possibilities, but I still haven't mastered it.

Please Login or Register  to view this content.

Artik

[jindon]

Delete

Please Login or Register  to view this content.

[therealdees]

@Jindon

Yes!!! It covered almost every case. I found a few more, but they're like 20 among 100k, and it's because it begins with "initials" like SQS, or something like that. It doesn't matter at all, I can deal with them and statistically it won't interfer, so I say the code works perfectly given the cases we aimed. Thank you very much, again!


About deleting

Please Login or Register  to view this content.

it didn't work. It still overruns other colors

This is what I ran:

Please Login or Register  to view this content.

[jindon]

Make sure that your RGB values are correct.

What happen if you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[therealdees]

Aj! I wrongly corrected a piece of code without checking it.
It should be better now, but not perfect.
Artik

Hey! This is great. It covered every case (almost sure). I went thru all the 100k rows with pgdown, and couldn't find a single missing case!

Thank you very very much! I will study the code as it seems quite complex and I have no idea how it works, so it's a good oportunity to learn some more

[therealdees]

@Jindon

I ctrl c ctrl v the RGB codes from another code, its correct.

About changing the @ line it gives me the message: $E$2. What is it?


EDIT: It gives me the cell address of every match I guess. If I click ok it keeps showing the rest

[jindon]

It picked up the one of the interior color matched in E2, so it should work.

Anyway, other code seems working, so stick with it.

[therealdees]

It picked up the one of the interior color matched in E2, so it should work.

Anyway, other code seems working, so stick with it.

Both are great! I appreciate your will to help! Will study em both

[Artik]

I will study the code as it seems quite complex

The code is as simple as a meter of twine in your pocket. It only has a lot of conditions to check.
"quite complex" these are RegExp patterns:

Please Login or Register  to view this content.

Artik