Text Format

cyliyu

New Member
Joined
Jul 7, 2015
Messages
14
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,396
Office Version
2010
Platform
Windows
I think you will need VBA event code to do what you want... where is the data being entered by your users?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
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

New Member
Joined
Jul 7, 2015
Messages
14
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

New Member
Joined
Jul 7, 2015
Messages
14
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,692
Office Version
365
Platform
Windows
Try changing your Data validation to

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

cyliyu

New Member
Joined
Jul 7, 2015
Messages
14
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.
 

cyliyu

New Member
Joined
Jul 7, 2015
Messages
14
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,692
Office Version
365
Platform
Windows
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?
 

Forum statistics

Threads
1,077,686
Messages
5,335,656
Members
399,032
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top