Search code examples
daxpowerpivot

Finding a number in text always gives an error

I have text datatype in a table column [Items], which always ends in a number:

Item 1
Item 3

Using a find formula in a calculated column works if I use text:

=FIND(" ",[Items])

But doesn't work if I use a number:

=FIND("1",[Items])

I have tried using FORMAT(1,"string"), tried looking for the number with and without quotes etc. I tried looking for " 1" with the space but nothing works if I include the number 1 in my formula.

Why not!? This type of behaviour isn't exhibited in Excel so makes it even more frustrating.

Solution

  • i think that you need to pass an argument for what it should return when it doesn't find anything:

    =FIND("1",[items],1,-1)