Return text after a string

[Madmartigan]

Within a text field there may be nulls, or text that contains the letters "LFU." I want a formula that searches for "LFU" in the string and then returns 6 characters starting in position 2 to the right of "LFU." If "LFU" cannot be found in the string I want to return "None."

Everything I try either errors or doesn't give me the desired result. This was my best shot (excluding the "None" part) and it errors:

Test: Right([Text],InStr([Text],"LFU")6)

[orange]

Try a comma , before the 6

Post a few sample records , so readers can understand the data.

[Madmartigan]

The comma before the 6 told resulted in the wrong number of arguments error.

Some sample records could be null as I mentioned. Also:

LFU 02/27/14

Desired result for this record would be "02/27/14"

01/17 - Terms should be Net 30

Desired result for this record would be "None"

Desired result for null, blank, or error would also be "None"



***correction, I said 6 characters, but it should be 8 to capture the "/"'s

[Madmartigan]

I got this to work:

Test:IIf(Left([Text],3)='LFU',Mid([Text],5,8),'None')

However, the only flaw is that it requires "LFU" to be in position 1 of any string. I'd like it to be such that it would find LFU anywhere in the string and work the same way.

[June7]

Consider:

If InStr([Text], "LFU"), Trim(Replace([Text], "LFU", ""))), "None")

[Madmartigan]

That would work, but there are situations where additional text can be found after the date. I wouldn't want that text included. Only the 8 characters after "LFU "

[orange]

I take it that this will be in a query, not vba.

Reqd: IIf(InStr(Trim(Text), "LFU") = 0, "None", Mid(Trim(Text), InStr(Trim(Text), "LFU") + 4, 8))

[Madmartigan]

Perfect, thanks!!

[orange]

Happy to help. Good luck with your project.

[Madmartigan]

Another question on this. Obviously what I'm trying to return is a date value. Could I try something like this? (I did this and it didn't work but you see the idea):

IIf(InStr(Trim([Text]),"LFU")=0,"None",Format(Mid(Trim([Text]),InStr(Trim([Text]),"LFU")+4,8)),"d/m/yy")

[June7]

You have misplaced paren:

IIf(InStr(Trim([Text]),"LFU")=0,"None",Format(Mid(Trim([Text]),InStr(Trim([Text]),"LFU")+3,8),"d/m/yy"))

[Madmartigan]

Fantastic - thank you!