Result of formula isn't a numer

Hi peeps!

I need some help with my formula in excel.

Here my example:

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"

excel

Formulas and Functions

SergeiBaklan
Mar 21, 2022
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.

9 Replies

HansVogelaar
MVP
Mar 21, 2022
Drclone

A shorter version of the formula:

=--TEKST.SAMENV(ALS(ISGETAL(--DEEL(A1,REEKS(LENGTE(A1)),1)),DEEL(A1,REEKS(LENGTE(A1)),1),""))
- Drclone
  Copper Contributor
  Mar 21, 2022
  This one doesn't work here hans.
  I've got an error
  - HansVogelaar
    MVP
    Mar 21, 2022
    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.
HansVogelaar
MVP
Mar 21, 2022
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.
- Drclone
  Copper Contributor
  Mar 21, 2022
  Wow both of the options work! Yours and the one of sergei. Consider this one absolutely solved
SergeiBaklan
Diamond Contributor
Mar 21, 2022
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.
- Drclone
  Copper Contributor
  Mar 21, 2022
  Wow! I've been looking for so long haha. Thank you!

Forum Discussion

Result of formula isn't a numer

9 Replies

Resources