Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
Ability to extract data from strings
Currently I am working on how to get Tasks Into my Database from email.
This is the simplest use for this. I would use it daily because most of my requests come to me in email... Cooper can you.....

Many times, I assign the task to someone else.. Then of course forget it!
I would need the following to make the task.
task assigned to
due by

I can make this work by forwarding the email to my database..with a tag "task"
then put a delimited string at the beginning
then I could use the Part formula to extract the fields to make the task.

The problem is, I am not the only person that wants to use this sort of function. Other people desperately want to forward tasks or conversations to tables in the database. But expecting a person to remember to correctly format a string is asking more than most people can handle.

I am thinking a formula that would extract using a beginning and ending delimiter would be very helpful

extractdata("text","start delimiter", "end delimiter")
end delimiter should default to RETURN.
have it return a FALSE if the delimiter cannot be found.

"I am sure I can do this using part(), contains(), left(), right() functions. But the logic would be cumbersome, and have to be specially made for each and every field.

Now we can have people send or forward emails. They would just need to put in the beginning.

Assign to: john
Priority: high
due: 30

task: xxxxx

While this is much more typing, its easier to remember.
Plus most email programs have auto text functions. "signature"

so a default FORWARD Task block can be made.

Assign to:


The user could fill in the lines and be good to go.

it would eliminate many errors.


cooper collier
Date Created
1/11/2016 9:36:34 AM
Date Updated
1/12/2016 8:21:16 AM
New Idea
Promoted By
gerardo garciacooper collier
Kirill Bondar  Staff  1/12/2016 5:10:45 AM
Left(NotLeft([Body], "Assign to:"), "\n") only 2 characters longer than proposed extractdata :)

cooper collier  1/12/2016 8:07:40 AM
Wicked!! Its always a pleasure seeing someone who makes the PRO in programmer!

I totally missed the NotLeft command! This does 100% perfect what I was proposing. It allows you to essentially pick out sections from a text string. I tested it with an email like this

priority: low
this is what goes in body

used the formula like this...Left(NotLeft([Body], "priority:"), "\n")

and it picked up "low" perfectly!

adding lower would take care of people like me who cannot consistently use the shift key..
Left(NotLeft(lower([Body]), "priority:"), "\n")

Using this its possible to test if the required information is in the email. Then actually reply back to the sender if its not... I discovered the "the address listed in column" notification setting will not recognize [from] but you can make a new formula email column set it to =[from] . then you can automatically send replies back to the sender.

This will make A LOT of people happy. Especially ME!
90% of my work comes to me in email... 90% of the time, I plan to add a task later. 90% of the time I totally forget to add the task. and 90% of the time, people yell at me because I forgot them!


Kirill Bondar  Staff  1/12/2016 8:15:39 AM
Trim() around the formula may help as the space after the colon would be picked up as a part of the value.
cooper collier  1/12/2016 8:21:16 AM
That is absolutely true!

adding that to my notes...
Its one of the things I try and "beat" into my fellow programmers head.
put Trim()_ around everything, it never hurts and solves hard to find bugs!
and here I am not thinking of it!


BACKUP or Dev Branch before touching anything!

Back to Search Results