Report Builder MID Function not defaulting to 'N/A'

[ldwf]

I am using a combination of INSTR and MID functions to search a text field for a specific value and if the value is found, I return everything after this text until a period is found. If the specific value is not found, I want to return "N/A" but instead get a value of #Error. If the value is found, it is correctly displayed but I can't get the 'N/A' to appear; it always returns #Error. I define two fields using INSTR function; one to show the first position that the value is found, and the second to show the position the period is in and use the values in the MID function. I noticed that the INSTR function returns an integer value of 0 if the value isn't found, so I write my MID function to check if the value is 0 and return 'N/A' if it is; i.e, IIF(Fields!Starting_Position_of_Text.Value=0, "N/A", MID(Fields!Comments.Value, Fields!Starting_Position_of_Text.Value, (Fields!Location_of_Period_in_Text.Value-Fields!Starting_Position_of_Text.Value))). This works fine if the text value is there but otherwise it shows #Error instead of 'N/A'.

[PhilipTreacy]

@ldwf 

If the substring is not found, so INSTR returns 0, what value is in Fields!Comments.Value ?

Phil

[ldwf]

The value in Fields!Comments.Value is some text, but each record may not have the text that I need to extract.  So if for example I am searching each record for a text value of 'Hello World' and need to extract it, the function does work fine if the field contains 'Hello World', but if it contains some other text like 'Hello Everyone', it shows Error# and I want it to show 'N/A'.  My Instr function to search for the first position of 'Hello World' does return a value of 0 if it can't find this in the text, so this is why my IIF is written the way it is.

[Ashish_Mathur]

Hi,

If you want a calculated column or a Power Query solution, then share some data to work with and show the expected result.

[ldwf]

Hi.  I am not permitted to upload any data.  I'll try to explain the problem more simple.  I have a text field and need to search for a value of 'Hello' in the text field and return this value and every character after it until a period is found.  So if the text says 'ABC Hello there.  WXYZ', I need to return 'Hello there'.  I use the Instr function to get the starting position, and another Instr function to get the position of where the period is.  I then have a field that uses the MID function to extract this text.  It works fine except when it can't find the text 'Hello'.  If the text field for any record doesn't have 'Hello', the function returns Error#.  I want it to return 'N/A'.   The INSTR function does assign a zero to the field called Starting_Position_of_Text if it can't find 'Hello', so I write my MID function as IIF(Fields!Starting_Position_of_Text.Value=0, "N/A", MID(Fields!Comments.Value, Fields!Starting_Position_of_Text.Value, (Fields!Location_of_Period_in_Text.Value-Fields!Starting_Position_of_Text.Value))).  But this always give me #Error instead of 'N/A'

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works.

Column = IFERROR(MID(Data[Text],SEARCH("Hello ",Data[Text]),SEARCH(".",Data[Text])-SEARCH("Hello ",Data[Text])),"N/A")

Hope this helps.