Discussions

Expand all | Collapse all

Extract data from a long text field

  • 1.  Extract data from a long text field

    Posted 07-01-2018 21:56
    How can I get the number 12 from this field 

    AIR WICK AIR FRESHENER 12/8 oz SNUGGLE FRESH LINEN

    https://www.screencast.com/t/1w1uGCphWs


  • 2.  RE: Extract data from a long text field

    Posted 07-01-2018 22:44
    Is this doable?

    I need it badly 


  • 3.  RE: Extract data from a long text field

    Top
    Contributor
    Posted 07-01-2018 22:53
    Ha ha - This old post may be of help:

    https://www.mrexcel.com/forum/excel-questions/1057397-how-extract-specific-data-cell-3.html">https://www.mrexcel.com/forum/excel-questions/1057397-how-extract-specific-data-cell-3.html">https://www.mrexcel.com/forum/excel-questions/1057397-how-extract-specific-data-cell-3.html

    There isn't enough of a pattern demonstrated from one field value to come up with a reliable parsing strategy. The string you entered could just be someone's random narrative description.


  • 4.  RE: Extract data from a long text field

    Posted 07-01-2018 22:48
    not "elegant", but I think it works.

    var text StripOffLeftText = Left([Supplier Description],"123456789");
    var number PositonOfFirstNumber = Length($StripOffLefttext)+1;
    var text FirstNumber = Mid([Supplier Description],$PositonOfFirstNumber,1);
    var text SecondNumber = Mid([Supplier Description],$PositonOfFirstNumber+1,1);
    var text ThirdNumber = Mid([Supplier Description],$PositonOfFirstNumber+2,1);

    ToNumber(
    List("",
    If(Contains("0123456789",$FirstNumber) and Trim($FirstNumber)<>"",$FirstNumber),
    If(Contains("0123456789",$SecondNumber) and Trim($SecondNumber)<>"" and Trim($FirstNumber)<>"",$SecondNumber),
    If(Contains("0123456789",$ThirdNumber) and Trim($ThirdNumber)<>"" and Trim($SecondNumber)<>"",$ThirdNumber))
    )


  • 5.  RE: Extract data from a long text field

    Posted 07-01-2018 22:51
    OK Batter, but now I have this issue, this is the text in the field 

    IR WICK FRESHMATIC REFILL 250 ML CS/12 ASSORTED

    It's coming out to 250 & I want to 12

    My main goal is to get how many items are per caes form this description 


  • 6.  RE: Extract data from a long text field

    Posted 07-01-2018 22:57
    I don't think it will be possible to have a perfect extraction formula as the data is inconsistent.

    In the example I did, I was looking for the first number from the left.

    In your next example you are looking for the first numbers from the right.

    I can make a formula for either, but how to know which is correct for each type of data


  • 7.  RE: Extract data from a long text field

    Posted 07-01-2018 22:59
    Can we make an if Statement? since the data I am getting is not consistent, the supplier keeps changing this, so I think I need to have all formulas in place & then make an IF Statement

    Can this work? 


  • 8.  RE: Extract data from a long text field

    Posted 07-01-2018 23:03
    Yes, I can make an IF statement, but how will the IF know which to use.  Dan's post on an excel forum looks like it may have been from you or one of your staff and it had the same fundamental problem.  how to know if we should use the right side digits or the left side digits and this is complicated by the fact that the item description may have digits in it which are not the case qty, but refer to the size of the item in oz or in ML.


  • 9.  RE: Extract data from a long text field

    Top
    Contributor
    Posted 07-01-2018 23:06
    It is a waste of time trying to write a QB formula to extract "items per case" from a couple of instances of narrative text. You need to look at many fields values to discern a pattern (if there is one). The pattern may involve regular expressions of machine learning for that matter.