I want to extract a specific piece of text from another multi line text field.

In the example above, what data are you looking to extract?  

I see three colons and no semi-colons.

I've corrected it to colons. After the colon. Let's take the text after Name: as an example of the text I want to extract (Which would be "QuickBase" in this example.

Ok. Now I get it. You want to extract the data into three separate fields. I will try to post the answer later this evening when I get a chance.

Thank you.

Hi Patrick,

So, if you ALWAYS will get that kind of values in the Multi Line Text field (named Basic Data) as

Name: QuickBase
Move Date: 9/15/2016
Phone: 000-000-0000 

And you will have 3 fields to store each value.

First formula field is Name. The code would be:    NotRight(Part([Basic Data], 2, ":"),9)

Second formula field is Move Date. The code would be:   NotRight(Part([Basic Data], 3, ":"),5)

and the third formula field is Phone. The code would be:     Right([Basic Data],":")

The first formula will look for the second colon and bring what's on its left before the first colon.

The second formula does the same but starting from the third colon.

The third formula is just looking to extract anything that is on the right side of a colon, but, since it is a multi line it will bring every thing on the right side of the last colon.

It is not a very fancy code, but I think that might work for you. Cheers.

Ricaro the Lion Tamer is mostly correct.  But I suggest wrapping his formulas in Trim(   ) to get rid of any leading or trailing spaces or other invisible characters.

That worked Thank you

You mean (NotRight(Part([Basic Data], 2, ":"),9)) as an example?

I mean like this

Trim(Right([Basic Data],":"))

QuickBaseCoach...  I think it should be TRIM(NotRight(Part([Basic Data], 2, ":"),9)) to get rid of the spaces between the colon and the value. If doing Trim(Right([Basic Data],":")) will only remove leading (before Name)and ending spaces (last phone digit..). At the end, it would look like:
TRIM(NotRight(Part([Basic Data], 2, ":"),9))
TRIM(NotRight(Part([Basic Data], 3, ":"),5))
TRIM(Right([Basic Data],":"))

Yes, that was my point.  Each of the 3 formulas needed to be wrapped in Trim (    )

So I've been working with your formulas but running into a problem. Here is an example of a multi line text field that I am trying to pull information from into separate fields.

Moving From: Oilton, OK 74052
Moving To: Oilton, OK 74052
Distance: 0 miles
Move Date: 09/28/2016
Move Type: One Bedroom Apartment
Weight: 3,500 lbs
Lead Information  Lead ID: 1543299
Lead Created: 09/18/2016 02:49 PM
Customer Name: Jennifer Smith
Phone Number: 555-55-5555
Email: deletedemail@gmail.com
If you have any questions, please don't hesitate to email Partner Care, your partner manager, or call (666) 666-6666

This problem applies to almost all of the lines of data I'm trying to extract from this one field but an example, when I use TRIM(NotRight(Part([Basic Data], 11, ":"),9)) to get the email field, depending on the length of the persons email I may end up not getting all the email or some of the next line of data. Is there another way to only get the info after the colon regardless of the length of the text I'm trying to get?

That last field can be accessed with this formula.

Left(Trim(Right([Basic Data],":")),"\n")

Hi Patrick,
This code: TRIM(NotRight(Part([Basic Data], 2, ":"),9))
The 2 is to look for that character and the order of text from right to left and top to buttom. The 9 is the amount of characters to ignore before the colon, then, take all the characters before the previous colon.
Example:
Moving From: Oilton, OK 12345
Moving To: Oilton, OK 56789
Distance: 0 miles
The colon in "Moving From:" is the colon # 1.
The colon in "Moving To:" is the colon # 2.
The colon in "Distance:" is colon # 3.
So when applying the formula, you need to count the amount of characters before the colon and ending after the last user entered characters.
For example:
"Moving From" has 11 charactes (include in the count the spaces but do not add the colon in to the count.
"Moving To" has 9 characters.
"Distance" has 8 characters.
Now that you have the info, when putting the formula, just update the numbers.

TRIM(NotRight(Part([Basic Data], 2, ":"),9))
The formula will look for the second colon (the colon in Moving To:), discard the 9 characters previous to the colon (Moving To) and take value all the way before the colon #1, which will be OK 74052.

TRIM(NotRight(Part([Basic Data], 3, ":"),8))
The formula will look for the colon #3, disregard the 8 characters before that colon #3 (Distance) and give you OK 56789.

For the last value, the email address, it is a bit more tricky. You will need to create 2 new formula text fields.
The first new field will have this: TRIM(Right([Basic Data],":"))
This will extract:

deletedemail@gmail.com
If you have any questions, please don't hesitate to email Partner Care, your partner manager, or call (666) 666-6666

The second formula field could be:
Trim(NotRight(Part([Preemail], 1, "If "),0))

Assuming that after the email address, the next line will always start with "If ".
Note that I added an empty space after if in the formula just in case there is a "if" in the email address. No space should be in the email address.
Hope that helps.

This was brilliant. I tested it on a few different leads and it worked for every test, even though the information after the colon's was different for each one. Once I grasped the methodology behind the formula from your explanations, it all came together. Thank you to all that contributed especially Mr Tsai.

If I wanted to extract only the numbers from a Phone field: (555) 555-5555 to generate an email where only 5555555555 would show, how do i do that?

Sergio, try this  (in my example tel2 was the tel number)

list("",
Part([tel2],1," ()-"),
Part([tel2],2," ()-"),
Part([tel2],3," ()-"),
Part([tel2],4," ()-"),
Part([tel2],5," ()-"))

I have a very similar use case as the original question here. I am synchronizing Gmail to a table, and one of the fields is the body of the email, with field name [Body]. These emails are website visitor form submissions, where each row of the body is started with a field label, followed by a colon (and sometimes a space or three) and then the value that I want to extract after the colon and before the end of the line. I have this process as a legacy app in Microsoft Access and I am wanting to move it into QB. The function I wrote in Access gets passed a field label name and finds it and grabs everything after the colon and before the carriage return end-of-line character for that row. I don't think that the carriage return character is available to me in QB functions.

The reason this is important is that I have thousands of different forms feeding this email account, and not all of them are set up identically. People aren't consistent in their setups and use of field label names, and not every form has the same questions or field labels.

In other words, it looks to me like the solutions above assume that there's a certain order to the field labels. Is there any way I can make this approach work by grabbing only the characters after the colon for a specified field label and only grab characters to the end of that line in the body containing that label?

Thanks,
Michael

Below are two example emails:

---------------------------------------------------------------------------

Reference #:    10195972
Status:    Complete

How can I help you?:    Do you fill adult cavities?
Name:    Juliet Smith
Email:   xyzzy@yahoo.com
Phone:    3224405555
FormSiteID: 3088
Date:    2017-04-18 10:28:13
Start Time:    2017-04-18 10:27:40
Finish Time:    2017-04-18 10:28:13
Duration:    0m 33s
User:    71.251.203.91
Browser:    Chrome
OS:    Mobile
Referrer:    http://www.cosmeticdentistryhoboken.com/

---------------------------------------------------------------------------

CALLID: 497094b8-244b-11e7-8108-23564e714fbd
CLIENT: 2340878
TRACKING NUMBER: 1 (855) 857-5555
SOURCE: Direct
TIME: 2017-04-18 9:25 am
NAME: Wendy Smith
STATUS: completed
NUMBER: 1 (303) 555-1212
DURATION: 2:30
ADDRESS: Denver CO
KEYPRESS: 
LISTEN: https://s3.amazonaws.com/recordings_2013/4bc0503c-244b-11e7-a1da-0aa1611.mp3

---------------------------------------------------------------------------

Okay I made a demo out of your data. It does not focus absolutely on your specific parsing issue (I think you only wanted to parse some scrap of information) but it should be obvious that with script you can parse anything. Also, this answer is more in line with some of my other posts where I promote the idea of creating custom fields (like the Chessboard field: https://community.quickbase.com/quickbase/topics/ima-grandmaster-does-quickbase-support-chessboard-f...) 

In the following application there are only two fields in the table: [Body] (a multi-line text field) and [Editor] (an image onload field).

Attribute / Value Field
https://haversineconsulting.quickbase.com/db/bmqa7exsh?a=td

When you edit a record, the image onload field [Editor] will magically expand into a series of <input>s on the form where you can edit any of the attributes listed in the [Body] field.



If you add a record and enter text using the same multiline colon separated attribute / value format you can later edit the record and the [Editor] field will once again expand into a series of <input>s.

https://haversineconsulting.quickbase.com/db/bmqa7exsh?a=nwr

The code could be substantially improved as I wrote most of it last night when the Meetup speaker got a little dull ...

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=580

I have to go now as I have a bunch of people asking for Tic Tac Toe fields, Checkers fields, Mahjong fields, Backgammon fields, Conway's Game of Life fields. If you need personal assistance parsing your data using script or have some other game board fields you want implemented feel free to contact me off-world using the information in my profile:

https://getsatisfaction.com/people/dandiebolt

I wonder if anyone needs a custom field for a good old game of ancient Egyptian Senet:

Very clever, Dan! Thanks for the ideas and inspiration.