Forum Discussion
Solved
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"...
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.
This one doesn't work here hans.
I've got an error
I've got an error
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 FunctionUse like this:
=ExtractNumber(A1)
You'll have to save the workbook as a macro-enabled workbook (.xlsm) and allow macros when you open it.
- Oh it works! I had to use ; instead of ,
Sorry, I use English-language settings, with comma as list separator.