Parsing text from imported excel

[We Can Deb]

Hello everyone,

 

I have been working on this for many weeks and unfortunately the newest road block has me stumped.  Thank you in advance for any help you can give me.

 

I have 4 text fields in my Event/Guest table: GuestNames, FoodRequest, SitWith and SpecialAccess. 

 

For the first time, I am importing this information from an excel spreadsheet downloaded from a vendor.  Unfortunately, all 4 questions and their answers are combined into one field, so the imported cell looks like this:

 

If you have purchased tickets for others, please list their names for check in and name tag purposes.: John Doe, Mary Smith | Are there any food allergies?: No | Who would you like to sit with at lunch?: Mary Doe | Do you have any special accessibility needs?: No

 

I have defined some calculation fields to parse the start and end of the questions/answers and it works--as long as the user answers every question. 

 

If the guest does NOT answer a question, nothing is sent in that place.  This means that the placement of the question/answers is not predictable.  Nor is the text that starts at the next question in the line. 

 

In an attempt to solve this complication, I have created another calculation field that takes the entire blurb and substitutes a carriage return for each "|", making it a value list.  I can search for the text string that is applicable, but how do I retrieve the value of the whole line? 

 

If I could do that, I could trim the question off and have my text answer for each of the fields I need.

 

There may be a better way to do it, and I'm open to suggestions.  Thank you again!

 

 

 

 

[comment]

if I understand your description correctly, the answer to any question can be found between the text of that question and the following "|" delimiter - provided you append an extra "I" delimiter to the end of your imported text. If so, you can extract the response to any one of the four known questions using the following formula =

Let ( [
question = "Who would you like to sit with at lunch?: " ;
text = Yourfield & "|" ;
pos = Position ( text ; question ; 1 ; 1 ) ;
start = pos + Length ( question ) ;
end = Position ( text ; "|" ; start ; 1 )
] ;
Case ( pos ; Middle ( text ; start ; end - start ) )
)

This will return "Mary Doe" using your above example input, and nothing if the given question does not appear in Yourfield at all.

 

 

--

Hopefully, this is a one-time glitch and for your next imports you will have a proper vehicle that will enable you to import the data directly - including creating individual related records for each guest.

[eos]

See the attached file for a scripted approach that lets you use non-calculated target fields.

ParsingLoopConsideringEmpty_eos.fmp12.zip

[We Can Deb]

Both excellent suggestions, I'm playing with each solution--both have their positives and work very well--and you are amazing to have responded so quickly.  Thank you very much--you rock!