Be sure comma is there and in the right place

[daniels012]

Ok in City state and Zip

I must have the comma next to the city.
We should alway be typing 2 letters for the state and numbers after the state.

So can I somehow find the numbers on the right to the left of them will be my 2 letter state than any text before that must have the comma following the last word of the first part of text. And maybe do Data Validation???
We might have:
(the underscore represents an unwanted space)
Conway, TN 12345
Conway_, TN 12345 ' Needs to be validated because of the space
Conway, TN 12345-1234
San Jose, GA 12354
San Jose_, GA 12354 ' Needs to be validated because of the space
Research Triangle Park, NC 12365
Or maybe an omitted comma?
San Jose GA 12354 'Validate because of no comma

Thank You in advance,
Michael

 

[jasonb75]

I tried this:


But now, I tried to enter wrong ones and right ones and it does not validate at all??

Any ideas?
Michael

Try the formula that schielrn suggested earlier, although you don't need all of it,

=MID(B4,SEARCH(",",B4)-1,1)<>" "

Will suffice.

The fact that it evaluates to an error means when you set it up means that it's working correctly.

 

[daniels012]

Here is the result using your formula:

San Jose, Ca 25853 True
San Jose , Ca 25854 False
San Jose Ca 25855 #Value
San Jose,Ca 25856 True

First off, I need True or false, that is why I was getting error.

I need the last one to be false.

MIchael

 

[jasonb75]

Here is the result using your formula:

San Jose, Ca 25853 True
San Jose , Ca 25854 False
San Jose Ca 25855 #Value
San Jose,Ca 25856 True

First off, I need True or false, that is why I was getting error.

I need the last one to be false.

MIchael

Testing for a trailing space wasn't mentioned in earlier posts.

Simply insert a space After the comma in the search string.

=MID(B4,SEARCH(", ",B4)-1,1)<>" "

You don't need true or false, you asked for this for use in data validation, so an error will reject the user input the same as false.

 

[daniels012]

I need a preceding space, a trailing space and NO spaces ALL to be validated.
So just this formula will not do that right?
DO I need to put 3 formulas to check that? IF so, HOw?
Michael D

 

[jasonb75]

I need a preceding space, a trailing space and NO spaces ALL to be validated.
So just this formula will not do that right?
DO I need to put 3 formulas to check that? IF so, HOw?
Michael D

That one single formula worked for me!

<TABLE style="WIDTH: 157pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=209><COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=145>San Jose, Ca 25853

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=middle>TRUE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>San Jose , Ca 25854</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=middle>FALSE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>San Jose Ca 25855</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=middle>#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>San Jose,Ca 25856

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2671375 class=xl66 align=middle>#VALUE!</TD></TR></TBODY></TABLE>

As I mentioned previously, an error, of any kind, is as good as false in data validation, in fact, anything other than true, is false.

 

[daniels012]

Oh I got the same results you did.
I need it to come up with TRUE or FALSE
NO errors
This is for validation.

Michael D

 

[jasonb75]

Oh I got the same results you did.
I need it to come up with TRUE or FALSE
NO errors
This is for validation.

Michael D

Are you using proper Excel data validation, or just a formula in the cell for you to do a visual check?

With proper validation, you do NOT need FALSE, only TRUE for valid entries.

When excel comes up with

The formula equates to an error do you want to continue?

Answer "Yes"

 

[daniels012]

OK,
Great to know.

Let me see how that works

 

[daniels012]

Nope,
I entered an improper city state and zip and it is not Validating any of them??MIchael

 

[jasonb75]

Thought we were validating the comma, and the chanrater before and after to ensure correct spacing.

Looking back at your first post I see there is you did mention the state and zip, but think you're going to have serious problems getting all that into validation for a single cell.

Personally I would go for 1 of 2 methods.

1:- use 4 columns, city?, state, zip, concatenate. This would be easier to validate.

2:- Validate using a worksheet_change event as you mentioned previously.

Any preference?