Can you guarantee that the pattern is always $(number)(space)on(space)quantity?
Here is a possible solution, but there are some problems.
Formula for AmtSaved =
Let ( [ START = Position(test::text;"$";1;1)+1 ;
END = Position(test::text;"on";1;1)-1]
Middle ( test::text ; START ; END-START )
Formula for Qty=
Let ( [ START = Position(test::text;"on";1;1)+3 ;
END = Position(test::text;" ";1;4)]
Middle ( test::text ; START ; END-START )
The problems that will arise are that you can never be sure your entry people won't make typing errors and if'/when they do, the formulas will fail.
I am sure others will have better solutions, but this is a start for you.
I have normalized this data in excel and imported, as I would expect to do...but if calculated assumptions could be made outside of this pattern, it would be nice. Normalizing doesn't scare me to keep it simple. There is only <100 records per week to deal with. The future hope is to import and have all data automated.
I may have overcomplicated this, but I built on MicheleOlson's solution a bit, to remove some of the user error possibilities. Your main key here is using the "Filter" function to get rid of all the extra text and boil it down so you can extract your data. Here is at least how I would go about getting this info:
Assuming your field says this:
Save $10 on 4 steak sauce 10oz+
I would use this code:
Let ( [
REMOVE_EVERYTHING_BEFORE_MONEY = Right (Coupon ; Length (Coupon) - Position(Coupon;"$";1;1)) ;
FILTER_NUMBERS_ONLY = Substitute (Filter (REMOVE_EVERYTHING_BEFORE_MONEY ; "0123456789 ") ; " " ; " " );
START_FIRST_NUMBER = 1 ;
LENGTH_FIRST_NUMBER = Position ( FILTER_NUMBERS_ONLY ; " " ; 1 ; 1 ) - 1 ;
START_SECOND_NUMBER = Position ( FILTER_NUMBERS_ONLY ; " " ; 1 ; 1 ) + 1 ;
LENGTH_SECOND_NUMBER = Position ( FILTER_NUMBERS_ONLY ; " " ; 1 ; 2 ) - START_SECOND_NUMBER
"Money: " & Left (FILTER_NUMBERS_ONLY ; LENGTH_FIRST_NUMBER) &¶&
"Quantity: " &
Middle ( FILTER_NUMBERS_ONLY ; START_SECOND_NUMBER ; LENGTH_SECOND_NUMBER)
And it will give you this result:
In my view it would be better to use the 'MiddleWords' and 'GetAsNumber' functions.
Money Amount = GetAsNumber(MiddleWords(Input Text;2;1))
Quantity = GetAsNumber(MiddleWords(Input Text;4;1))
The 'GetAsNumber' will remove the '$' sign and any other punctuation around the 'Quantity'.
I must admit to not testing these two formulas, but they look like they will work ...
Best wishes - Alan Stirling, London UK.
PS: I have just tested my formulas and there is one situation where the 'Money Amount' formula returns the wrong value;
'Save $.50 on 4 straws' must be written as 'Save $0.50 on 4 Straws', since if the leading zero is missing, the decimal point is not picked up, giving the result of '50'!
The 'MiddleWords' function collects punctuation between alpha-numeric characters, but not before the start, or at the end of the 'word'.
Since I assume that you have control over how this list of offers is set up, you can ensure that leading zeros are entered where necessary.
There are already custom functions created for these occasions on briandunning.com, such as the before and after functions.
You might want to take a look at them.
Sent from my iPad
To handle the multiple data-sets within the single field, you will need script or custom function and each data-set should be a record with two fields (Dollars and Qty). I cannot assist you with the actual transfer until we are clear on whether the records should be created in the existing table or as related records. You might wish to take a look at the attached file (you must sign into Tech Net to download it). It uses a custom function to pull the values and in example file attached, data result is (first number is dollars, second is quantity):
This takes into account negative numbers, decimals - it should work in all situations (and not necessary to add a leading 0 for decimals) and it generates an error if the field contains an uneven number of "$" in comparison to " on " so you know if you need to correct the field first. I have included two additional calculations which show how to then pull the values from this calc. So you can use the calc to view your data and make sure it is fine before 'moving it on' or you can just use same logic directly in a script for extracting the pieces as you loop and create records.
If you tell us how you need to move this information, I would be happy to help you with a final script to 'make it so' otherwise I hope this gives you enough to accomplish your task.
Message was edited by: LaRetta - Also, I dropped the dollar sign. That isn't necessary.
Parse.fp7.zip 5.9 K
LOL, well I read this "These are coupons typed into one field" as meaning it was a multi-line text string with all the examples in it.
It would sure make a difference in the solution suggested.
Sorry guys I should've been more clear. Each coupon IS a separate record. I am also entering weekly deals from stores in another dbs and matching with available coupons. I've automated most, like category, sub-cat and store section when manf name is entered. As well as calls to manage expirations, etc. Thanks again for all the help.
Good to know! I've been doing this workaround forever, will be happy to try it out!