web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Electronic Reporting (...
Finance | Project Operations, Human Resources, ...
Suggested answer

Electronic Reporting (ER): Find substrings, show characters right from index of substrings

(1) ShareShare
ReportReport
Posted on by Honk 8
Dear community!
 
I should have posted this question long before trying to solve it by myself. Meanwhile I even doubt it is possible in ER, but maybe the swarm intelligence of this community proves me wrong. I really hope it does..
So here goes:
 
I need to alter the content of a field containing a string of characters, for example:
Producer (Hardware) = Company Contoso Products-123123SPEC = BMQ3321,ASSR G4545 SPECCUST = ABP234234-43
What is now required is to find only the values for the attributes SPEC and SPECCUST, omitting all characters before, and also the attributes and the equal-symbols themselves...
It should look like:
BMQ3321,ASSR G4545  ABP234234-43
Is there any way in ER to do this?
 
Before I involve the same developer who concatenated this abomination in the first place (and then it would lead to lots of questions, documentation, more time and a system update) I was really hoping there might be a way with using a formula. 
 
P.S.
I had come up with 
REPLACE(REPLACE(STRINGVALUE,"SPECCUST = ","",false),"SPEC = "," ",false)
which solved the initial requirement before it was specified to this now... any help is appreciated!
 
Thanks in advance!
 
edit: redundant wording
Categories:
Dynamics 365 Finance
I have the same question (0)
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,351 Super User 2025 Season 2 on at
    Electronic Reporting (ER): Find substrings, show characters right from index of substrings

    The ER formula language doesn’t directly support complex string parsing.  However, with a combination of SEARCH, RIGHT, LEFT, and REPLACE functions, you can get close to the desired output. Here’s a method to extract the values for "SPEC" and "SPECCUST" without the attributes and equal signs.

    Given your input:

     
    java
    Producer (Hardware) = Company Contoso Products-123123SPEC = BMQ3321,ASSR G4545 SPECCUST = ABP234234-43

    Approach

    1. Find the Position of "SPEC":
      Use SEARCH("SPEC = ", STRINGVALUE, 1) to locate where "SPEC" starts.

    2. Extract Right from "SPEC":
      After finding "SPEC", use RIGHT to extract everything after "SPEC = ".

    3. Remove Extra Text After "SPEC" Value:
      Find where "SPECCUST" starts, so you can remove any extra characters after the "SPEC" value and before "SPECCUST".

    4. Apply Similar Steps for "SPECCUST":
      Repeat steps 1–3 to get the value after "SPECCUST = ".

    5. Concatenate and Trim:
      Combine the extracted values for "SPEC" and "SPECCUST" and ensure there are no extra spaces.

    Example Formula

     
    plaintext
    SPEC_Value := LEFT(
    RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPEC = ", STRINGVALUE) - 6),
    SEARCH(" ", RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPEC = ", STRINGVALUE) - 6)) - 1
    );

    SPECCUST_Value := RIGHT(
    RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPECCUST = ", STRINGVALUE) - 9),
    SEARCH(" ", RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPECCUST = ", STRINGVALUE) - 9)) - 1
    );

    Concatenated_Value := TRIM(CONCATENATE(SPEC_Value, " ", SPECCUST_Value))

    Explanation

    • SPEC_Value: Finds the substring after "SPEC = " and captures only the characters up to the next space.
    • SPECCUST_Value: Locates "SPECCUST = ", then extracts everything after it up to the next space.
    • Concatenated_Value: Combines these values with a space between them.
  • Honk Profile Picture
    Honk 8 on at
    Electronic Reporting (ER): Find substrings, show characters right from index of substrings
    Hi Saif,
     
    thank you for the quick reply and your explanation! However, your syntax would not work with the ER formula language.
    There is no SEARCH() function and variables cannot be initialized as well.. while I appreciate your insight very much, I'm afraid this won't help me.
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 684 Super User 2025 Season 2

#2
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 588

#3
Martin Dráb Profile Picture

Martin Dráb 542 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans