Text Format

[cyliyu]

I have a sheet for user to keyin the data.
The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099.
The number after the "/" is always 4 digits, it was a data code.
The issue was end-user sometimes forgot to put a "space" before and/or after "/".
how can i custom the cell format to auto check and add the space?

 

[cyliyu]

Is it possible that an entry in the correct format of "xxx / xxx" does not exist in column A of Master? If that is possible, do you want the DV to allow or disallow the entry?

The master sheet was maintained and check daily by the store supervisor and validated by the manager.
There is a chance the entry by the end-user cannot be found in the master list, therefore, it should disallow.

 

[Peter_SSs]

So try this in your DV

=AND(COUNTIF($D$6:$K$30,D6)=1,ISNUMBER(FIND(" / ",D6)),VLOOKUP(D6,Master!$A$2:$F$1000,6,0)<>"supplier 1",VLOOKUP(D6,Master!$A$2:$F$1000,6,0)<>"supplier 2")

 

[cyliyu]

So try this in your DV

=AND(COUNTIF($D$6:$K$30,D6)=1,ISNUMBER(FIND(" / ",D6)),VLOOKUP(D6,Master!$A$2:$F$1000,6,0)<>"supplier 1",VLOOKUP(D6,Master!$A$2:$F$1000,6,0)<>"supplier 2")

Thanks & appreciate your help.
Didn't know I can use so many formula in the DV.

 

[Peter_SSs]

Thanks & appreciate your help.
Didn't know I can use so many formula in the DV.

You're welcome. Glad it worked for you.

 

[cyliyu]

I have one last question.
We using the copy and paste special -> validation to other cells. it is not working.
I have to manually replace all the 200 cells. it that normal when using such formula?

 

[Peter_SSs]

We using the copy and paste special -> validation to other cells. it is not working.

1. Where are you copying from?
2. Where are you pasting to?
3. What are the symptoms of "it is not working"?
4. Did you further alter the DV formula in any way?

With the DV formula suggested in cell D6, I was able to Copy - Paste Special (Validation) to the other cells in the range D6:K30

 

[cyliyu]

Whiling waiting for the reply, I manage to find the error.
A "=" sign was missing. sorry for the troubles.

 

[Peter_SSs]

Cheers. Glad you got it sorted. Thanks for letting us know.