Extract data from a long text field

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
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
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb
Is this doable?

I need it badly 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
Ha ha - This old post may be of help:

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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
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))
)
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb
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 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
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
Photo of Sam Krausz

Sam Krausz

  • 470 Points 250 badge 2x thumb
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? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,442 Points 20k badge 2x thumb
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.