How To Validate Text Data In Excel

This article will show you some examples of how to validate text data input in excel cells.

1. The first example shows you how to restrict the input text length.
2. The second example shows you how to allow input text only in the excel cell.
3. The third example shows you how to allow input text that starts or ends with specified text.
4. The fourth example shows you how to allow input text that contains specified text.

1. How To Restrict Input Text Length In Excel.

1. Select cell A1 in the excel worksheet.
2. Click the Data tab —> Data Tools group —> Data Validation menu item to open the Data Validation dialog window.
3. Click the Settings tab, and select Text length from the Allow drop-down list.
4. Select the item greater than from the Data drop-down list.
5. Input number 6 in the Minimum input text box.
6. Click the Input Message tab, then input the text Input Text in the Title text box, and input the text The text length should be greater than 6. in the Input message text area.
7. Click the Error Alert tab, and select Stop from the Style drop-down list.
8. Input the text Text length is not valid in the Title text box.
9. Input the text The input text length should be greater than 6. in the Error message text area.
10. Click the OK button to create the data validation rule.
11. Select cell A1 and drag the small green square on the bottom right corner of cell A1 to apply the text data validation rule to other cells.
12. You can select the other cells and click the Data tab —> Data Tools group —> Data Validation item to verify that the data validation rule has been applied to the cells.
13. Now when you select the cell that applies the above validation rule, it will show a yellow tip message dialog beside the cell.
14. If you input a text that length is not greater than 6 and press the OK button, it will show an alert dialog window.

2. How To Allow Input Text Only In Excel Cells.

1. Open the excel Data Validation dialog window.
2. Click the Settings tab.
3. Select Custom from the Allow drop-down list.
4. Input the formula =ISTEXT(A1) in the Formula text box.
5. Input the title and message in the Input Message and Error Alert tab text fields.
6. Then when the user inputs a none text value, it will alert a dialog window.

3. How To Allow Input Text That Start/End With Specific Characters.

1. If you only allow users to input text beginning or ending with a specific string, you can use the COUNTIF function.
2. Select the target cells in the excel worksheet, then click the Data tab —> Data Tools group —> Data Validation item, it will open the Data Validation dialog window.
3. Select the item Custom from the Allow drop-down list in the Settings tab.
4. In the Formula text box, input the formula =COUNTIF(A1,”PYTHON*”) + COUNTIF(A1,”*JAVA”) in it.
5. The above formula will only allow the cell text to start with python or end with java ( case insensitive ).
6. The * character after PYTHON or before JAVA can match 0 or more other characters.
7. The + character between the above 2 COUNTIF functions means OR relationship, which means either python-1 or ABC-java is the valid text by this rule.
8. The COUNTIF function is case insensitive, if you want to create a case-sensitive validation formula, you can combine LEFT, RIGHT, and EXACT functions together like below.
9. Combine LEFT and EXACT to create a case-sensitive start-with validation formula like this =EXACT(LEFT(A1,6), “PYTHON”).
10. Combine RIGHT and EXACT to create a case-sensitive end-with validation formula like this =EXACT(RIGHT(A1,4), “JAVA”).
11. Create a formula that allows either start with PYTHON ( case-sensitive ) or end with JAVA ( case-sensitive ), you can use the formula =EXACT(LEFT(A1,6), “PYTHON”) + EXACT(RIGHT(A1,4), “JAVA”).

4. How To Allow Input Text That Contains Specific String Only.

1. Sometimes, you may only allow the user to input text that contains the specified string in an excel cell, then you can combine the excel functions ISNUMBER, SEARCH, and FIND.
2. The function SEARCH(text, cell) will search the provided text in the cell value, if locate the text in the cell value then return a number to represent the text position in the cell. The SEARCH function is case-insensitive.
3. The function FIND(text, cell) is similar to the function SEARCH(text, cell), but it is case-sensitive.
4. The function ISNUMBER checks whether the returned value by the function SEARCH or FIND is a numeric value or not, if they return a number then that means the specified text is found in the cell.
5. You can open the Data Validation dialog window.
6. Click the Settings tab, and select the item Custom from the Allow list.
7. Input the formula =ISNUMBER(SEARCH(“python”, A1)) + ISNUMBER(FIND(“JAVA”, A1)) in the Formula text box.
8. Now when you enter the text hello-python in cell A1, it is allowed, but when you input text hello-java in cell A1, it will prompt an error dialog because you should use JAVA to replace java.