Adding After & Before Dash: Array Formula or VBA?
See Mr Excel and excelisfun use two methods to solve this very difficult adding problem: you must add numbers from a column of text strings where you need to add the number of egg cartons before the dash but then add the total number of eggs from after the dash and if the count is greater than 30 eggs then add 1 carton otherwise add any eggs less than 30. See these two methods:
1. VBA Custom Function
2. Array Formula that uses the functions LEFT, SEARCH, REPLACE, SUMPRODUCT, QUOTIENT, and MOD and uses “any math operation on text numbers to convert back to numbers” and uses the clip board to piece together formula elements into a large array formula and uses Find and Replace to substitute one formula element for another in a large formula. Also learn about function argument array operation.