Discussions

Expand all | Collapse all

How can I derive a name in the middle of text?

  • 1.  How can I derive a name in the middle of text?

    Posted 05-18-2018 00:43
    We have a block of text coming in from Quickbooks. (Description on invoice line item)

    There is always a line in the description that says 

    Consultant: First Last

    Where First and Last are their first and last names.

    There may be other things above it, and below it.

    Is there any way to parse this block of text and derive "First Last" as the result?

    I tried using both of these but the first one fails if there is more than one colon, and the second one fails if this line is not the third line.

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

    Trim(Right(Part([Description], 3, "\n"),":"))

    Can this be done in native QuickBooks? 


  • 2.  RE: How can I derive a name in the middle of text?

     
    Posted 05-18-2018 03:28
    How many colons could you possibly get in the invoice line description?
    If it's only a handful, you could check the text to the left of each colon to see if it is "Consultant", and if it is, use the text to the right. Something like this:

    //Get all the text parts between colons(:)
    var text texta = Part([Descsription],1,":");
    var text textb = Part([Descsription],2,":");
    var text textc = Part([Descsription],3,":");
    var text textd = Part([Descsription],3,":");

    //Find which part contains "Consultant", and then use the next part.
    var text NamePart = 
    Case("Consultant",
    Right($texta,10),$textb,
    Right($textb,10),$textc,
    Right($textc,10),$textd);

    //Get the first and last name
    var text FirstName = Part($NamePart,2," \n");
    var text LastName =  Part($NamePart,3," \n");

    //Create the display anme
    $FirstName & " " & $LastName


  • 3.  RE: How can I derive a name in the middle of text?

    Posted 05-18-2018 17:33
    There could be a lot of colons in some cases.

    I'm wondering if there is any way to do this with a code fragment / code page.


  • 4.  RE: How can I derive a name in the middle of text?

     
    Posted 05-19-2018 01:05
    Absolutely,

    If this parsing is at all complex, and you have the stomach for javascript, I would create a code page that utilizes handy and powerful JS libraries like Lodash">https://lodash.com/docs/4.17.10">Lodash to parse the content and return what you need.

    Do you have any specific examples of the data you are looking at?  It would be helpful if you could share some sanitized examples.

    Thanks,


  • 5.  RE: How can I derive a name in the middle of text?

    Posted 05-18-2018 23:59
     Building on Matt's approach, you can use the new line character which is \n as a delimiter.

    //Get all the text parts between colons(:)
    var text texta = Trim(Part([Descsription],1,"\n"));
    var text textb = Trim(Part([Descsription],2,"\n"));
    var text textc = Trim(Part([Descsription],3,"\n"));
    var text textd = Trim(Part([Descsription],3,"n"));


    var text ConsultantLine = 
    IF(
    Begins($texta, "Consultant"), $texta,
    Begins($textb, "Consultant"), $textb,
    Begins($textc, "Consultant"), $textc,
    Begins($textd, "Consultant"), $texta)

    Trim(Right($ConsultantLine,":"))


  • 6.  RE: How can I derive a name in the middle of text?

    Posted 05-19-2018 01:53
    Thanks everyone.  I was actually able to get the sending application configured to make the text much easier  for native QuickBase to parse.  I didn't think that was an option.