Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    3

    Unanswered: formula need to extract a portion of a field

    Using CR 10
    Field type: Memo

    Problem: I have a memo field that begins with:
    ABC Company, Fort Worth, Texas,
    The client wants the report field to be empty if it only contains the above, otherwise they want to remainder of the memo field to print excluding the above. Of course the user has not entered the data uniquely...sometimes it starts with:
    ABC Company, Fort Worth, Texas, (or)
    ABC Company, Fort Worth, Texas, (or)
    ABC Company, Fort Worth, Texas,
    You get the idea. Ideally it would be nice for them not to have to go back & uniform their entry. But of course that's not their choice. If a formula is not workable with the data entered in the various methods, please suggest a formula that will work with the one of the entries.

    I was trying a "iif" formula with a nested "right" but just couldn't get it to work.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2004
    Location
    Connecticut
    Posts
    85
    You could try "contains". If the text might be repeated in the memo, you should be able to make a string from the first 100 (or whatever number) characters of the memo field and then see if that contains the text.

  3. #3
    Join Date
    Apr 2006
    Posts
    3

    contains?

    Unfamiliar with "contains" could you give an example.

  4. #4
    Join Date
    Jul 2005
    Posts
    50
    What you ask seems very simple, as far as excluding a static string value is concerned. In the place where they want the String value you indicate above excluded, put a formula that has this code inside it:

    Replace({tb_PartialExclude.Name and Address}, "ABC Company, Fort Worth, Texas", "", 1)

    (The last paramter is an empty string inside double quotes.)

    This should give you the results you ask for.

    "ABC Company, Fort Worth, Texas, Bottling Division" will yield: "Bottling Division"

    "ABC Company, Fort Worth, Texas, Chemical Division" will yield: "Chemical Division"

  5. #5
    Join Date
    Apr 2006
    Posts
    3
    Thank you.

  6. #6
    Join Date
    Jul 2005
    Posts
    50
    Glad I could help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •