Formulae for cellls

G

Gogan

New member
Joined
Jun 11, 2016
Messages
5
Reaction score
0
Points
0
Hi all, I'm not as advanced as some of you here and I'd need some help with something.
Basically I need Column A to restrict the data input to a certain value. The value I need it to be is in the form of AB12345678. That's 2 letters at the beginning followed by 8 numbers.
Can anyone help?
Thank you
 
Gogan said:
That's 2 letters at the beginning followed by 8 numbers.
Click to expand...
'Data Validation' may limit the number of characters to enter in the cell. Try next
- tab Data
- Data Validation
- Text length, equal to, 10
 
Restrict enter format data into cells (2 letters+8 numbers)

Gogan said:
The value I need it to be is in the form of AB12345678. That's 2 letters at the beginning followed by 8 numbers.
Click to expand...
Previous post refers to the length of characters entered data.
If you want to limit strictly defined format (2 letters + 8 numbers characters)
Select all range of cells and put this formula in the Data Validation
Code: 
=IF(AND(IF(ISTEXT(A1),TRUE,FALSE),ISNUMBER(VALUE(RIGHT(A1,8))))=TRUE,TRUE,FALSE)
 

Attachments

  • limitformatdata-gogan.xlsx
    9.1 KB · Views: 9
Last edited:
Thanks navic for this, it's really helpful.
Is it possible to restrict, in the same formula, the value of the letters?
I need the selected cells to only accept LM650984 (where LM6 is constant and the remaining 5 numbers are variables).
Am I being too much of a pain in the backside? :)
 
Gogan said:
I need the selected cells to only accept LM650984 (where LM6 is constant and the remaining 5 numbers are variables).
Click to expand...
If the letters in the first three characters (LM6) on the upper or lower case sensitive, then use the following formula in Data validation
Code: 
=IF(AND(LEN(A1)=8,IF(ISTEXT(A1),TRUE,FALSE)=TRUE,ISNUMBER(VALUE(RIGHT(A1,5)))=TRUE,EXACT(LEFT(A1,3),"LM6")=TRUE)=TRUE,TRUE,FALSE)
The formula will not accept lower-case letters, example 'lm612345'
 
Thank you again navic for your help.

It doesn't seem to work that formulae. First of all I've messed up with the length of the text, which is LM61234567 (so LM followed by 8 numbers, of which first one is always 6 - LM61111111, LM61111112, LM61111113 etc).
This would be under the form of a barcode, scanned in excel using a barcode scanner. The barcode might be slightly unreadable and scan LM6111111$ (add a special character), which I want to avoid, and that is why I wanted a formulae to pop an error up every time that happens.
On the formulae I got from you, I can type anything in the cell and it would let it.
Is it way too complicated? I could validate the cells using text length (barcode sometimes adds extra character), but then I'd still have issues with special characters.
Thank you for your help so far, and if you would consider helping me if it's not too hard, I'd really appreciate it!
 
Gogan said:
That's 2 letters at the beginning followed by 8 numbers.
Click to expand...
Gogan said:
(where LM6 is constant and the remaining 5 numbers are variables)
Click to expand...
Gogan said:
(so LM followed by 8 numbers, of which first one is always 6 - LM61111111, LM61111112, LM61111113 etc)
Click to expand...
I do not understand you.
Since the beginning of topics you're not set any example of your expected data and all possible situations of problem?

First, you asked two letters + 8 digit numbers (see your post # 1)
Second, You asked for a different problem. You have a constant 'LM6' + 5digit numbers (see your post # 4)
Third, again a change in demand. Now you have a constant 'LM6' + 7 digit numbers

Which of the following data from list below is not acceptable?

AB12345678
ab12345678
LM12345678
LM612345678
LM612345
LM61234567
lm61234537
LM61111111
lm61111111

P.S. There is another problem. Are the first two letters case sensitive ('LM' or 'Lm')
In the attachment of this post, is an example of 'LM6' + 5 digit numbers (You change the formula for +7 digit numbers)

For example you have three formula
With EXACT function (Accepts only 'LM' uppercase)
Code: 
=IF(AND(LEN(A1)=8,IF(ISTEXT(A1),TRUE,FALSE)=TRUE,ISNUMBER(VALUE(RIGHT(A1,5)))=TRUE,EXACT(LEFT(A1,3),"LM6")=TRUE)=TRUE,TRUE,FALSE)
With FIND function (Accepts only 'LM' uppercase)
Code: 
=IF(AND(LEN(A1)=8,IF(ISTEXT(A1),TRUE,FALSE)=TRUE,ISNUMBER(VALUE(RIGHT(A1,5)))=TRUE,IFERROR(IF(FIND("LM6",LEFT(A1,3),1)=1,TRUE,FALSE),FALSE))=TRUE,TRUE,FALSE)
With SEARCH function (Accepts 'LM' and 'lm', not paying attention to uppercase and lowercase letters)
Code: 
=IF(AND(LEN(A1)=8,IF(ISTEXT(A1),TRUE,FALSE)=TRUE,ISNUMBER(VALUE(RIGHT(A1,5)))=TRUE,IFERROR(IF(SEARCH("LM6",LEFT(A1,3),1)=1,TRUE,FALSE),FALSE))=TRUE,TRUE,FALSE)
 

Attachments

  • limitformatdata-gogan(1).xlsx
    13.5 KB · Views: 10
I apologise for all the messing around. I need it to accept exactly, in upper case, 10 digits, of which LM6 is a constant, then followed by 7 other numbers. I need to limit it at that and not accept any extra or less characters, not accept special characters, and to give a warning when data entered is invalid.
 
Restrict format entry data in cells by Data Validation

Gogan said:
I need it to accept exactly, in upper case, 10 digits, of which LM6 is a constant, then followed by 7 other numbers. I need to limit it at that and not accept any extra or less characters, not accept special characters, and to give a warning when data entered is invalid.
Click to expand...
navic #7 said:
In the attachment of this post, is an example of 'LM6' + 5 digit numbers (You change the formula for +7 digit numbers)
Click to expand...
Ok :)
The formula is as follows (my solution in the example files)
Code: 
=IF(AND(LEN(A1)=10,IF(ISTEXT(A1),TRUE,FALSE)=TRUE,ISNUMBER(VALUE(RIGHT(A1,7)))=TRUE,EXACT(LEFT(A1,3),"LM6")=TRUE)=TRUE,TRUE,FALSE)
 

Attachments

  • limitformatdata-gogan(2).xlsx
    9.6 KB · Views: 8
Last edited:
Perhaps shorten it a bit to

=AND(LEN(A1)=10,ISNUMBER(RIGHT(A1,7)+0),EXACT(LEFT(A1,3),"LM6"))

(unchecked)
 
This worked absolutely perfect!
Thank you so much.
 
You must log in or register to reply here.
Back
Top