SOLVED

# Result of formula isn't a numer

Occasional Contributor

# Result of formula isn't a numer

Hi peeps!

I need some help with my formula in excel.

Here my example:

 f5 5 no ONWAAR

The f5 is in row A, 5 row B etc.

So whats going on?

Row A

In the first cel you see "f5" with it my work cel, I need to get rid of all the letters. No matter if it's an A, B, C, F or whatever. In my example an f.

In row B, i've got an formula that remove all the letters. Formula: =KLEINE.LETTERS(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(A1;"a";);"b";);"c";);"d";);"e";);"f";);"g";);"h";);"i";);"j";);"k";);"l";);"m";);"n";);"o";);"p";);"q";);"r";);"s";);"t";);"u";);"v";);"w";);"x";);"y";);"z";);"A";);"B";);"C";);"F";);"S";);"W";);" ";))

Row C

=IF(B1=5;"yes";"no")

Row D

Checks if cel B is an number with formula.

=ISNB(B1)

Problem:

I can't calculate with the result. The result is in my example a 5, but in the formula excel still says "no" that should be a "yes"

9 Replies
best response confirmed by Drclone (Occasional Contributor)
Solution

# Re: Result of formula isn't a numer

You compare text with number, it always "no". It could be

``=IF(B1="5";"yes";"no")``

or add double dash in front of substitutes formula.

# Re: Result of formula isn't a numer

Change the formula to

=--SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(KLEINE.LETTERS(A1);"a";);"b";);"c";);"d";);"e";);"f";);"g";);"h";);"i";);"j";);"k";);"l";);"m";);"n";);"o";);"p";);"q";);"r";);"s";);"t";);"u";);"v";);"w";);"x";);"y";);"z";);"A";);"B";);"C";);"F";);"S";);"W";);" ";)

Note that I moved KLEINE.LETTERS to immediately before A1.

# Re: Result of formula isn't a numer

Wow! I've been looking for so long haha. Thank you!

# Re: Result of formula isn't a numer

Wow both of the options work! Yours and the one of sergei. Consider this one absolutely solved

# Re: Result of formula isn't a numer

A shorter version of the formula:

=--TEKST.SAMENV(ALS(ISGETAL(--DEEL(A1,REEKS(LENGTE(A1)),1)),DEEL(A1,REEKS(LENGTE(A1)),1),""))

# Re: Result of formula isn't a numer

This one doesn't work here hans.
I've got an error

# Re: Result of formula isn't a numer

It works in Microsoft 365 and Office 2021, not in older versions.

In Office 2019, you can use (confirmed with Ctrl+Shift+Enter):

=--TEKST.SAMENV(ALS(ISGETAL(--DEEL(A1,RIJ(INDIRECT("1:"&LENGTE(A1))),1)),DEEL(A1,REEKS(LENGTE(A1)),1),""))

In older versions, you can create a custom VBA function in the Visual Basic Editor:

``````Function ExtractNumber(s As String) As Long
Dim v As String
Dim i As Long
For i = 1 To Len(s)
If IsNumeric(Mid(s, i, 1)) Then
v = v & Mid(s, i, 1)
End If
Next i
If v <> "" Then
ExtractNumber = Val(v)
End If
End Function``````

Use like this:

=ExtractNumber(A1)

You'll have to save the workbook as a macro-enabled workbook (.xlsm) and allow macros when you open it.