How to use len AND search at the same time?

[Mr2017]

Hi

I'd like to use the len and search functions in ONE formula.

Scenario: I have a spreadsheet where users can input up to 4 characters in a column and they mean a specific thing eg "Jane Fonda"

But then they'd only mean that if they contained EITHER a single number or a combination of numbers and a full stop eg 5.50

Does anyone know how I can create a formula that counts the number of characters in a cell AND checks to see if the cell has at least one number?

If the cell didn't contain any numbers, then I'd want to return the word "non-numeric."

To give you a simple example,

"1" or "5.50" in cell A1 should return "Jane Fonda" in cell B1
But ABC in cell A2 should return "non-numeric" in cell B2.

I've used the LEN function to count the number of characters in a cell, but the problem is that if someone types in anything random that's less than 5 characters, then the formula fails - so I need to check two conditions:

i) less than 5 characters
ii) AND contains a number

Thanks in advance.

 

[jtakw]

You're welcome, thanks for the feedback.