Check if 2 commas exist

[Nichole09]

Hello!!

Is there a way to check if two commas exist in a cell? In cell H3 of my worksheet users will enter an address and I want to make sure they use two commas when entering an address as another sheet takes this address and splits it. for example: 123 ABC st Moraga CA 97501 should read, 123 ABC st, Moraga, CA 97501. If the user does not enter two commas in cell H3 I would like a msgbox to appear instructing the user to enter a comma after the street address and after the city and the cell contents to be cleared.

possible workaround: the reason for this, is another sheet splits this address up to show the street address in one cell, and the cell below that will contain the city, state and zip. I haven't found a way using a formula or vba to split the address without the use of commas. As sometimes there will be a city with two names and every street address entered could be different. Using commas seemed to be the best solution, unless someone can provide another solution?

Any help would be greatly appreciated!!

 

[Rick Rothstein]

You could use this Data Validation formula...

=LEN(H3)-LEN(SUBSTITUTE(H3,",",""))=2

 

[Nichole09]

You could use this Data Validation formula...

=LEN(H3)-LEN(SUBSTITUTE(H3,",",""))=2

This is really Cool! I actually have never used a formula for data validation. This works perfectly! Is there a way to clear the contents if this error message appears? Perhaps a macro to run if this error message pops up?

 

[Rick Rothstein]

This is really Cool! I actually have never used a formula for data validation. This works perfectly! Is there a way to clear the contents if this error message appears? Perhaps a macro to run if this error message pops up?

You would need remove the Cell Validation and use VBA event code to do that, but I'm curious... why would you want to clear the cell? The idea of warning the user and allowing him/her to see what was typed is two-fold... first, it allows the user to review what he/she typed to see where they went wrong and, second, it allows the user to edit the cell without having to type the entire entry over again.

 

[Nichole09]

This is a very good point! Having read this it makes the most sense to leave it be. I tried to incorrectly type in the address and did not realize this error message would continue to appear. I was worried they could click out of this message and perhaps forget to make corrections. If a user where to not make this correction the address would not split correctly on another sheet. This sheet gets printed as a PDF then emailed to another company. So I was worried of the address missing characters. After attempting to enter the address incorrectly (more than once this time) I noticed this error will continue to pop up. This is certainly the better way to go! Thank you Rick!!