Result of formula isn't a numer

[Drclone]

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"

[#2 · Accepted solution]

@Drclone 

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.

[#3]

@Drclone 

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.

[#4]

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

[#5]

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

[#6]

@Drclone 

A shorter version of the formula:

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

[#7]

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

[#8]

@Drclone 

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.

[#9]

Oh it works! I had to use ; instead of ,

[#10]

@Drclone 

Sorry, I use English-language settings, with comma as list separator.