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.

 

[Mr2017]

To make things easier, I'll add pre-existing formulae:

So if the number 1 is in cell A1, then this formula returns the words "Jane Fonda": =IF(LEN(A1<5),"Jane Fonda","")

If the words "a number" appear in cell A1, then this formula returns the words "Jane Fonda": =IF(ISNUMBER(SEARCH("a number",A1)),"Jane Fonda","")

However, I'd like to modify the second formula so that it searches for a number rather than a phrase ie does the cell contain a number?

And then after modifying the second formula, I'd like to combine it with the first, so that I have one formula that counts the number of characters in a cell to check if they're less than 5 AND it also checks to see if that cell has a numerical figure. If both those conditions aren't met, then the formula would return a blank.

 

[jim may]

Here's a User Defined Function (UDF) which determines if a cell value included a number anywhere in it.


Post this into a Standard Module - and in your worksheet just like any other formula enter =ExtractNum(A1)>0 where A1 = XYZ123 -- should yield TRUE
If A1 = ABCXYZ -- #VALUE !

Function ExtractNum(c) As Long
Dim i As Integer
Dim MyNum As String
'Returning numeric value from string'
    MyNum = ""
    For i = 1 To Len(c)
        If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) > 0 Then
        MyNum = MyNum + Mid(c, i, 1)
        End If
    Next i
ExtractNum = MyNum
End Function

 

[tyija1995]

This formula will input "Jane Fonda" if there is a number in the target cell & length of 4 or less (apologies its a bit messy!)

=IF(AND(LEN(A1)<5,OR(IFERROR(SEARCH(0,A1),FALSE),IFERROR(SEARCH(1,A1),FALSE),IFERROR(SEARCH(2,A1),FALSE),IFERROR(SEARCH(3,A1),FALSE),IFERROR(SEARCH(4,A1),FALSE),IFERROR(SEARCH(5,A1),FALSE),IFERROR(SEARCH(6,A1),FALSE),IFERROR(SEARCH(7,A1),FALSE),IFERROR(SEARCH(8,A1),FALSE),IFERROR(SEARCH(9,A1),FALSE))),"Jane Fonda","non-numeric")

I have assumed that A1 has the target value e.g. "ABC" - change this as appropriate.

 

[Special-K99]

I've deleted my solution, I dunno what I was thinking

 

[Mr2017]

Cool - thanks! That's one of the longest formulas that I've ever seen in my life!!

 

[Mr2017]

Thanks Jim - do you know if the function would work if I sent the workbook to another user or another organisation ie a client?

 

[jim may]

Of course, since the file includs a Macro it will have to be saved with an ".XLSM" extension. So the Macro is WITHIN the file.

Once others get the file from you and save it to their Computers when they OPEN IT - they likely will get a Message (which often folks
miss as it is SO SMALL to see) saying "ENABLE-MACROS?" -- They should reply Yes, or the like so the UDF will work for them. Not
Answering "Yes" to Enable Macros? will Result in an ERROR if they try entering in a cell =

ExtractNum(A1) for example.

Hope that helps.

Jim
​

 

[jtakw]

That's one of the longest formulas that I've ever seen in my life!!

Hi,

It need not be...

This formula will accomplish what you described:

Book1
	A	B
1	5.50	Jane Fonda
2	abc	Non-Numeric
3	a2cd	Jane Fonda
4	ab3de	Non-Numeric
Sheet649

Cell Formulas
Range	Formula
B1	=IF(AND(SUM(--(ISNUMBER(MID(A1,{1,2,3,4},1)+0))),LEN(A1)<5),"Jane Fonda","Non-Numeric")

 

[Mr2017]

Awesome! Thank you!!!