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
AIR WICK AIR FRESHENER 12/8 oz SNUGGLE FRESH LINEN
https://www.screencast.com/t/1w1uGCphWs
 470 Points
Posted 12 months ago
QuickBaseCoach App Dev./Training, Champion
 69,914 Points
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))
)
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))
)
 470 Points
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
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
QuickBaseCoach App Dev./Training, Champion
 69,914 Points
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
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
 470 Points
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?
Can this work?
QuickBaseCoach App Dev./Training, Champion
 69,914 Points
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.
Ⲇanom the ultimate (Dan Diebolt), Champion
 30,204 Points
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.
Related Categories

Formulas & functions
 3020 Conversations
 80 Followers
Ⲇanom the ultimate (Dan Diebolt), Champion
https://www.mrexcel.com/forum/excelquestions/1057397howextractspecificdatacell3.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.