can't get a formula that"s supposed to only return the numbers in a cell to work

[still learning]

Hi
I’m using excel 365

B2:B153 is a list of numbers
E3 is a result of a VLOOKUP using the B2:B153 and what is in E1

In E5 I have the following to give me where the number in E3 came from

=CELL("address",INDEX($B$2:$B$153,MATCH(E3,$B$2:$B$153,1)))

I then tried to use this in F5, and I got #NAME?

=IF(E5="","",onlynums(E5))

I tried a different formula and still get #NAME?

=@IF(E5="","",StripChars(E5))

I then tried the two by entering the cell address without the >>=cell(…

And got the same results.

What Am I missing



Mike

 

[Joe4]

I believe "onlynums" and "StripChars" are probably custom user defined functions (UDF) that someone created.
So you would have to get the VBA code for that function and put it in your workbook in order to use it.

 

[JamesCanale]

Guess:

MrExcelPlayground6.xlsx
	B	C	D	E
1				4
2	1
3	2			4
4	3
5	4			$B$5
6	5
7	6
8	7
9	8
10	9
11	10
Sheet8

Cell Formulas
Range	Formula
E3	E3	=VLOOKUP(E1,B2:B11,1,0)
E5	E5	=CELL("address",OFFSET(B1,MATCH(E1,B2:B11,0),0))

 

[still learning]

Yep it is a UDF
After I saw your reply, I went thru all the modulars in the workbook that I found the formula in and found it.
I don't remember where I got it from. >>> old differently affects memory
In case anyone is looking for it after seeing this post, here it is.

Function OnlyNums(sWord As String)      
    Dim sChar As String
    Dim x As Integer
    Dim sTemp As String
    sTemp = ""
    For x = 1 To Len(sWord)
        sChar = Mid(sWord, x, 1)
        If Asc(sChar) >= 48 And _
          Asc(sChar) <= 57 Then
            sTemp = sTemp & sChar
        End If
    Next
OnlyNums = Val(sTemp)
End Function

and

Function StripChar(aText As String)  
    Dim I As Integer
    StripChar = ""
    For I = 1 To Len(aText)
        aChar = Mid(aText, I, 1)
        Select Case aChar
            Case "0" To "9"
                StripChar = StripChar & aChar
        End Select
    Next
End Function