Discussions

 View Only
Expand all | Collapse all

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

Patrick Patrick

Patrick Patrick09-15-2016 21:35

Patrick Patrick

Patrick Patrick09-16-2016 00:35

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training09-16-2016 00:37

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

    Posted 09-15-2016 18:57
    Say the multi-line text field is called [Data] and it contains the following lines of text:

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

    The info before the colons will always be the same (this is customer lead data that is synced in from GMail). I want to extract the info after the colons into it's own field.

    How do I do this?


  • 2.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-15-2016 20:38
    In the example above, what data are you looking to extract?  

    I see three colons and no semi-colons.


  • 3.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-15-2016 20:50
    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.


  • 4.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-15-2016 21:17
    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.


  • 5.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-15-2016 21:35
    Thank you.


  • 6.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-15-2016 22:21
    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.


  • 7.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-15-2016 23:28
    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.


  • 8.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-16-2016 00:35
    That worked Thank you


  • 9.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-16-2016 00:35
    You mean (NotRight(Part([Basic Data], 2, ":"),9)) as an example?


  • 10.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-16-2016 00:37
    I mean like this

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


  • 11.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-16-2016 00:49
    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],":"))


  • 12.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-16-2016 02:04
    Yes, that was my point.  Each of the 3 formulas needed to be wrapped in Trim (    )


  • 13.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-19-2016 16:52
    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?


  • 14.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-19-2016 18:36
    That last field can be accessed with this formula.

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


  • 15.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-20-2016 16:39
    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.


  • 16.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-20-2016 18:56
    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.


  • 17.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 12-29-2016 17:27
    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?


  • 18.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 12-29-2016 18:59
    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," ()-"))


  • 19.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 04-18-2017 15:54
    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

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


  • 20.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 04-20-2017 13:23
    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:



  • 21.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 04-27-2017 16:55
    Very clever, Dan! Thanks for the ideas and inspiration.


  • 22.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-06-2019 08:40
    What if the data is in the multi-line text with only a comma and no space?

    ------------------------------
    Jessica Adkins
    ------------------------------



  • 23.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 09-06-2019 13:30
    You would have to post a representative set of data and what you are trying to parse out.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 24.  RE: I want to extract a specific piece of text from another multi line text field.

    Posted 04-24-2020 09:36
    I know it is quite old post (I found also 2 from 2017 and 2015) but I believe I found quite Elegant way to overcome QuickBase "limitation" in terms of finding text string vs "text value containing all the possible delimiter characters"

    What I did was using SearchAndReplace formula to Look for specific text and replace it with special character which I do not expect normaly in text  (% or #)​
    In this case:
    Name:    Juliet Smith
    Email:   xyzzy@yahoo.com
    Phone:    3224405555

    Length(Left(SearchAndReplace([TextAbove],"Email: ",#"),"#"))

    Than it gives where # mark is available and using MID and other SearchAndReplace formula you can find where MID should Start and End.

    I had Quite Long thinking but works.



    ------------------------------
    Adam Krzyzanek
    ------------------------------