CODEWITHSUNDEEP
regexvalidationgoogle-sheetsstring-formattingspreadsheet
zongshita
zongshita

Reputation: 191

How to use Data Validation, Number must start with 7

I want to find a formula that can validate the order number. if the future order numer entered does not start with 7****, it will show a warning. Thank you so much for your help.

https://docs.google.com/spreadsheets/d/1piS3GQ5TzrGAr4VSoSbABkMa-fRm6n_wP16RimegO6E/edit?usp=sharing

enter image description here

Upvotes: 2

Views: 530

Answers (2)

player0
player0

Reputation: 1

use:

=REGEXMATCH(""&A2, "^7.+")

enter image description here

Upvotes: 1

CCCC
CCCC

Reputation: 251

Select the range of cells that you only allow texts that start or end with certain characters.

Click Data > Data Validation > Data Validation.

In the Data Validation dialog box, please configure as follows.

1 Select Custom from the Allow drop-down list; 2 For allowing texts that start with certain characters, please copy the below formula into the Formula box; =EXACT(LEFT(A2,3),"KTE")

And for allowing texts that end with certain characters, please copy the below formula into the Formula box; =EXACT(RIGHT(A2,3),"KTE") 3 Click the OK button.

Notes:

  1. In the formulas, A2 is the first cell of the selected range; 3 is the number of characters you specified, KTE is the start or end text.

  2. These two formulas are case-sensitive.

  3. If you don’t need case-sensitive, please apply the below CONTIF formulas: Only allow texts that start with KTE in a range of cells =COUNTIF(A2,"KTE*") Only allow texts that end with KTE in a range of cells =COUNTIF(A2,"*KTE")

  4. And then, click OK button. From now on, only the text string begins or ends with the centain characters you specified can be entered into the selected cells.

In your case, replace KTE with 7 since you want yours to start with number 7. Yours should be: =EXACT(RIGHT(A2,1),"7") You can remove the quotation marks housing the number 7 since 7 is an int, not a string (varchar)

=EXACT(RIGHT(A2,1),7) maybe you can try this if the former fails.

Upvotes: 3

Related Questions