Extracting data from one field into 8 fields

[philipcaplan]

I have a field called 'input' an example of whose content is shown below. I need to extract 8 pieces of information out of this, each into 8 separate fields named as follows:
extract_entry_number
extract_date_submitted
extract_region_chosen
extract_headline
extract_wording
extract_filename
extract_email

Can you please provide the best/easiest way (calculation?function?) for each of these 8 fields to be populated from the content of 'input'

(Note: any of the 8 pieces of data 'input' can be of arbitrary lengths in different records)

(Note: lines of text starting "Entry #:" and "Date Submitted:" and "Which" and "and which" and "Create a" and "Your wording" and "Upload an image" and "Your email" will always be the same every time)

//////Example content for 'input'////////
Entry #:3
Date Submitted:02/04/2019 00:06
Which CATEGORY would you like your Ad to be in?
Category chosenxxx
and which Region? (leave blank for All London)
Region chosenxxx
Create a Headline for your Ad (up to 40 letters)
Headline herexxx
Your wording (maximum 140 letters + optional small image) or you can upload a JPEG or PDF file of your complete Ad.
Wording of up to 140 chars herexxx
Upload an image or logo file here... (JPG or PDF)
jfa-favicon.png
2.36 KB
Your email address (required) - will be used for confirmation
[email protected]
Delete Edit
 

[comment]

Here's how you can extract the category:

Let ( [
prefix = "Category chosen" ;
pos = Position ( input ; prefix ; 1 ; 1 ) ;
start = pos + Length ( prefix ) ;
end = Position ( input ; ¶ ; start ; 1 )
] ;
Middle ( input ; start ; end - start )
)

To extract the other parameters, just change the prefix variable.

 

 

Edited April 2, 2019 by comment

[philipcaplan]

Thank you comment, but I believe you have misunderstood my data and my need......

Firstly, perhaps I should explain that the content of 'input' is copied-and-pasted from an online website-form result.
The form has 6 fields which have data entered by a customer, plus 2 (Entry# and Date Submitted) which are added by the form-software.

For example the line saying "Which CATEGORY would you like your Ad to be in?" is something which will always be the same in every record.

The line saying "Category chosenxxx" was my test input to that field in the online form. SO
-- THIS IS THE TEXT that will vary from record to record
-- and is the text I want 'extracted' to be the content of the field 'extract_category' (which I actually forgot to include in the 8 fields I wanted!!!)

If the line starting "Which CATEGORY etc etc" has to be included in full to be used that will do. But I would prefer if just the first 2 words were sufficient to identify it.

 

 

[comment]

I am not sure I understand your clarification. If the fixed text preceding the content of the category is the line:

Which CATEGORY would you like your Ad to be in?

then define the prefix as:

prefix = "Which CATEGORY would you like your Ad to be in?¶" ;

to extract everything on the subsequent line (note that this is assuming every item is exactly one line long).

 

50 minutes ago, philipcaplan said:

If the line starting "Which CATEGORY etc etc" has to be included in full to be used that will do. But I would prefer if just the first 2 words were sufficient to identify it.

I don't see why, but if the first two words are sufficient to uniquely identify it, you could do something like (I will make my own example here):

input:

YOUR NAME GOES HERE
John Smith
YOUR ADDRESS BELOW THIS
123 Maple Street
WHICH CITY DO YOU LIVE IN
Twin Peeks

Calculation extracting the address =

Let ( [
searchValue = "YOUR ADDRESS" ;
i = ValueCount ( Left ( input ; Position ( ¶ & input ; ¶ & searchValue ; 1 ; 1 ) ) )
] ;
GetValue ( input ; i + 1 )
)
 

 

[philipcaplan]

Thank you comment.

The first one worked perfectly **AFTER** I included an "end of para" character at the end of the search string!!

And the second one worked equally well, with the advantage that if I later make small changes to the web-form labels I don't have to remember to redo the FM db, as long as the first few (unique) words remain unchanged!!

Thanks again.

[comment]

4 minutes ago, philipcaplan said:

The first one worked perfectly **AFTER** I included an "end of para" character at the end of the search string!!

Look closer, I have that.

[philipcaplan]

Hi. Could I ask for another version?

At present, the "second one" extracts the text up the end of the line.

What I need **also** is a version of that code which extracts all text (including any 'returns') from the beginning until the occurrence of specific text (such as "Image Choices")

[comment]

7 hours ago, philipcaplan said:

extracts all text (including any 'returns') from the beginning until the occurrence of specific text

Not sure if you mean from the beginning of the entire input or from the prefix.

To get all text before searchString, use:

Left ( text ; Position ( text ; searchString ; 1 ; 1 ) - 1 )

For a generic formula to extract text between known prefix and suffix see:
https://fmforums.com/topic/59491-advanced-text-calculation/?do=findComment&comment=281168
https://fmforums.com/topic/99696-pulling-works-cited-from-google-scholar/?do=findComment&comment=453780

 

[philipcaplan]

Sorry that I was not clear.

Assuming the following as part of 'input':

Your wording .......other text........ up to 140 characters
This is the 1st line of the customer's wording [return]
And this is the 2nd line [return]
And this the 3rd & final thing the customer typed [maybe a return]
Upload an image

what I need is a calculation field which extracts the following:
the text starting with "This is....." and ending with "the customer typed" [plus return if there is one]

In other words, the "start" of extraction is marked by a line beginning "Your wording"
and the "end" is marked by a line beginning "Upload an image"

[comment]

46 minutes ago, philipcaplan said:

In other words, the "start" of extraction is marked by a line beginning "Your wording"

In yet other words, the start is marked by the position of the first carriage return after "¶Your wording". Which can be found by =

lineStart = Position ( text ; "¶Your wording" ; 1 ; 1 ) ;
lineEnd = Position ( text ; ¶ ; lineStart + 1 ; 1 ) ;
start = lineEnd + 1 ;

 

[philipcaplan]

Thanks comment. That doesn't quite work as I expected!

Here's my calculation:
Let ( [ lineStart = Position ( input ; "¶Your wording (up to" ; 1 ; 1 ) ; lineEnd = Position ( input ; ¶ ; lineStart + 1 ; 1 ) ; start = lineEnd + 1 ; end = "Image Choices" ] ; Middle ( input ; start ; (LineEnd - LineStart)))

My 'input' includes the following text:

Your wording (up to 500 letters/70 words)
This is my wording of up to 70 words.
On several lines.
Optionally finishing with a return.
Image Choices
A small image (your Wording will run around it)

The result I get is 2 lines only, truncated after the 4th char of the 2nd line:
"This is my wording of up to 70 words.
On s"
when what I expected was all 3 lines:
"This is my wording of up to 70 words.
On several lines.
Optionally finishing with a return."

What have I done wrong??

[comment]

1 hour ago, philipcaplan said:

What have I done wrong??

You mean other than not indenting your formula for readability? Two things:

 

1. This makes no sense: 

end = "Image Choices" 

It needs to be:

end = Position ( input ; "Image Choices" ; start ; 1 )

or even better:

end = Position ( input ; "¶Image Choices" ; start ; 1 )

 

2. Once you have the start and the end, you need to do =

Middle ( input ; start ; end - start )

 

So in the end =

Let ( [
lineStart = Position ( input ; "¶Your wording (up to" ; 1 ; 1 ) ;
lineEnd = Position ( input ; ¶ ; lineStart + 1 ; 1 ) ;
start = lineEnd + 1 ;
end = Position ( input ; "¶Image Choices" ; start ; 1 )
] ;
Middle ( input ; start ; end - start )
)