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?

 

[Rick Rothstein]

I think you will need VBA event code to do what you want... where is the data being entered by your users?

 

[Fazza]

is data validation adequate? can identify non-compliant entries. it won't add a missing space, though
basic idea =ISNUMBER(FIND(" / ",A1))
and you could make it more complex

 

[cyliyu]

is data validation adequate? can identify non-compliant entries. it won't add a missing space, though
basic idea =ISNUMBER(FIND(" / ",A1))
and you could make it more complex

I already have the data validation set to make sure no duplicated entry.
https://ibb.co/6rSnC8p


What i need is to check to make sure a "space" is auto-enter if end-user forgotten, otherwise, the data validation will not work, it will still accept 0030 / 0007 and 0030/0007 as 2 different entry.

I also looking into to check the data tally with the master list, item sent out for supplier shouldn't accept too.
VBA code is acceptable. Thanks.
https://ibb.co/8bt5GGR

 

[cyliyu]

I already have the data validation set to make sure no duplicated entry.
https://ibb.co/6rSnC8p


What i need is to check to make sure a "space" is auto-enter if end-user forgotten, otherwise, the data validation will not work, it will still accept 0030 / 0007 and 0030/0007 as 2 different entry.

I also looking into to check the data tally with the master list, item sent out for supplier shouldn't accept too.
VBA code is acceptable. Thanks.
https://ibb.co/8bt5GGR

I am using this code to check Duplicate entry in data validation

=COUNTIF($D$6:$K$30,D6)=1

 

[Peter_SSs]

Try changing your Data validation to

=AND(COUNTIF($D$6:$K$30,D6)=1,ISNUMBER(FIND(" / ",D6)))

 

[cyliyu]

Try changing your Data validation to

=AND(COUNTIF($D$6:$K$30,D6)=1,ISNUMBER(FIND(" / ",D6)))

Thanks, it works.
I have to check how to validate the input keyed in and reject it if it is found to be at supplier1/2 factory.

 

[Peter_SSs]

Thanks, it works.

You're welcome.

I have to check how to validate the input keyed in and reject it if it is found to be at supplier1/2 factory.

I don't know what you mean by that.

 

[cyliyu]

You're welcome.

I don't know what you mean by that.

Basically, there are 3 conditions to check when the end user entered the data.
The first 2, which are duplicate and serial number format (" / ") has completed.
I have a master sheet consists all the serial number (at least 500) and their status, Column A was the serial number and Column F was the status.
I need to validate when the end user enter the serial number, is the serial number (equipment) physically in the store or already sent to supplier for repair.
if the status in column F indicated "supplier 1" or "supplier 2", then it should reject end user entry as well.

 

[Peter_SSs]

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?