CODEWITHSUNDEEP
excelvalidation
Tom
Tom

Reputation: 1618

Excel perform specific validation on cells

I'm looking to try and do two separate types of validation.

  1. Is it possible to configure validation on a cell to check the first character in that cell and it must be a letter ?

Eg. Anything starting with A-Z case insensitive is fine.

  1. Is is possible to set a cell so it can only contain a number ?

I've got the number check working using =ISNUMBER(J1) that seems to only allow numbers.

I'm trying to check the first character and have tried: =ISTEXT(LEFT(B10,1)) but that doesn't seem to do what I was hoping..

Thanks

Upvotes: 1

Views: 194

Answers (2)

teylyn
teylyn

Reputation: 35915

Checking if the whole cell is text is easy with just a simple formula, but if you want to check for only the first letter, then it's more involved.

To ensure that the first character is NOT a number, you can use the custom validation formula

=NOT(ISNUMBER(SUM(LEFT(A1,1),0)))

That will still allow special characters llike $ or & etc. as the first character. If you only want to allow a-z and A-Z as the first letter, then use

=AND(CODE(LEFT(A1,1))>=56,CODE(LEFT(A1,1))<=122)

enter image description here

To allow only numbers, use the built in validation options for Decimal or Whole Number.

enter image description here

Upvotes: 1

Omar Munoz
Omar Munoz

Reputation: 19

  • Select one or more cells to validate.
  • Open the Data Validation dialog box.
  • For this, click the Data Validation button on the Data tab, in the Data Tools group or press the key sequence Alt > D > L (each key is to be pressed separately).
  • On the Settings tab of the Data Validation dialog window, select Custom in the Allow box, and enter your data validation formula in the Formula box.
  • Click OK.

For text only in a cell build a custom rule with the ISTEXT function, for example:

=ISTEXT(D2)

Custom rule for numbers only in a cell using ISNUMBER.

=ISNUMBER(D2)

Reference: https://www.ablebits.com/office-addins-blog/2017/08/17/use-data-validation-excel-custom-rules-formulas/

Upvotes: 0

Related Questions