You can use the leftWords and MiddleWords functions to get what you want.
LeftWords(text; 1) will return all characters to the left of the first "|" in text
MiddleWords( text; 2; 1)
will return the text between the 1st and 2nd "|" characters.
Edit note: changed functions when I relized the | character was recognized as a word separator.
Ordinarily that would be an even better, simpler solution. In fact, it worked perfectly at first.
But in case others are following this, I wanted to point out that in this solution the text string represents user-defined Project Identifiers (text based), and we need reasonably to expect the user will include space, hyphen, #, and possibly other characters that also act as word separators. In that event, the function stops too soon.
Of course we could require the Project I.D. to be strictly numeric and get around it, but I am afraid that would be too restrictive; e.g., 101-A, #445, HR Training, etc. might be desired input.
Which makes my original, pre-edit suggestion the better approach. Reposting it for anyone reading this thread:
Left (text; Position(text; "|" ; 1 ; 1) - 1) will return text left of the first |
Middle ( text; Position ( text ; Position ( text ; "|" ; 1 ; 1 ) + 1) ; Position ( text ; "|" ; 1 ; 2 ) - Position ( text ; "|" ; 1 ; 1 ) ) will return text between the 1st and 2nd |.
I was reading this one and wondered if this is the best method to solve my issue, im trying to look for a word in a text field and return that text.
Fields: Discription and Item
Please order 15 pens
I will need a black biro
I want it to extract "pen" or "biro"
not sure if this thread is still being followed or updated....
I have similar situation which I really could do with help...
I want to extract the data shown in red and placed in a sepearate field from the string below; this is exactly how the data is currently presented
Any help or suggestions of how this can be achieved are gratefully welcomed