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

 

[schielrn]

This is maybe one solution?

Excel Workbook
	A	B
1	Conway, TN 12345	No problems
2	Conway , TN 12345	Needs to be validated because of the space
3	Conway, TN 12345-1234	No problems
4	San Jose, GA 12354	No problems
5	San Jose , GA 12354	Needs to be validated because of the space
6	Research Triangle Park, NC 12365	No problems
7	San Jose GA 12354	Validate because of no comma
Sheet3

Cell Formulas
Range	Formula
B1	=IF(ISERROR(SEARCH(",",A1)),"Validate because of no comma",IF(MID(A1,SEARCH(",",A1)-1,1)=" ","Needs to be validated because of the space","No problems"))

Not sure it will catch all of your problems though? I guess you could check if the value before the comma was a letter if you really wanted to?

 

[daniels012]

Nice!
Thank You
schielrn

 

[daniels012]

Can I do this in Data Validation?
Or should I use VBA On_Worksheet_Change
All my cells next to this are being used.

Michael

 

[schielrn]

You should be able to use this for data validation maybe?

=and(isnumber(SEARCH(",",A1)),MID(A1,SEARCH(",",A1)-1,1)<>" ")

 

[daniels012]

Gives an error when I try in validation.

Michael

 

[schielrn]

What is the error? It works for me.

 

[daniels012]

It says the value equates to an error do you want to continue.
This is a file that has to be for excel 2000, 2003 and 2007 would that be why maybe it is not working?

Michael

 

[daniels012]

bump

 

[daniels012]

I tried this:

=IF(ISERROR(AND(ISNUMBER(SEARCH(",",B4)),MID(B4,SEARCH(",",B4)-1,1)<>" ")),FALSE,AND(ISNUMBER(SEARCH(",",B4)),MID(B4,SEARCH(",",B4)-1,1)<>" "))

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

Any ideas?
Michael