Changing the iserror to an InStr = 0 allows for an initial value without a + to be handled correctly
#Func! Error I can’t get around
I have a column of data ([Premium Hdr].[Rev]) that contains blanks, letters and strings containing letters+numbers. I need to do a Nz on the blanks, trim the + and numbers if they are there, and if there are just letters, return that in a calculated column.
Column:
blank
A
B+1
C+23
CCB+2
My statement handles the Nz stuff fine, handles the + trim fine, but returns a #func! if there isn’t a + for the statement to work against.
IIF(IsNull([Prem Hdr].[Rev]),””,IIf(IsError(Left(Nz([Prem Hdr].[Rev],””),InStr(Nz([Prem Hdr].[Rev],””),”+”)-1)),[Prem Hdr].[Rev],Left(Nz(Prem Hdr].[Rev],””),InStr(Nz([Prem Hdr].[Rev],””),”+”)-1)))