Help With Summing some columns and helper columns
Hello! I have a rather odd situation I am trying to find a solution for but having some trouble.
Columns:
 Column A
 This is a text field that I am pulling out text from with a MID function to get a count
 I cannot edit this data as it is being imported from an outside source
 Column B
 This is a Formula column I am pulling out the quantity from with the following formula
 =IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), 1)), 0)
Issues:
 I had to add the value into the formula so that SUM functions would work properly
 If the quantity is greater than 10 the formula above breaks because it no longer contains a value
 If I add "Column C" I can make it so that one has the count for more than 2 digits, but I am having trouble coming up with a formula to add the columns together properly
Any help would be much appreciated. 😁
Best Answer

It did end up being a single misplaced parenthesis. Sorry abouut that.
=IFERROR(VALUE(MID([Column A]@row, 10 + FIND("Quantity: ", [Column A]@row), FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row))  (10 + FIND("Quantity: ", [Column A]@row)))), 0)
Answers

Are you able to provide some screenshots for context?

In this example the QTY should be 50 but because of the character issues its coming up 55

Where is "Column A"? I'm not sure I follow exactly what you are trying to do?

My Apologies, I cannot show everything as there is some personal information in the feilds,
Column A is formatted like this
In column B (QTY1) I was trying to pull out the number after "Quantity: " which I am able to accomplish with this formula
 =IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), 1)), 0)
This formula fails if the quantity is greater than 10 due to having 2 digits. like the example below
So I tried to correct this by using Column C (Qty11) by using the following formula
 =IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), 2)), 0)
But this fails on every device with a quantity less than 10 due to it looking for the second digit
So I was trying to figure out a smart way to do this so I can get the number correctly but just can't wrap my head around it.
I know its a lot thanks for your help

Ah. Understood. Seeing the data in Column A helps.
First we find the starting point (which you have). Then we find the line break after (which is CHAR(10) in formulas) and subtract from it the starting point.
Starting Point:
(10 + FIND("Quantity: ", [Column A]@row))
Line break after starting point:
FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row)
Subtract start from end:
FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row)  (10 + FIND("Quantity: ", [Column A]@row))
Use that as the number of characters in the MID function to make it more dynamic:
=IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row)  (10 + FIND("Quantity: ", [Column A]@row)))), 0)

Thanks Paul!
I was having some trouble putting that in, it tells me the Syntax is not quite right, is there some parenthesis I may need to move?

What do you get if you plug it in as a cell formula instead of a column formula?

#Incorrect Argument Set
Here is a test sheet with examples in it
https://app.smartsheet.com/b/publish?EQBCT=95c7ad94e69149d18f3e694c45bacaab

It did end up being a single misplaced parenthesis. Sorry abouut that.
=IFERROR(VALUE(MID([Column A]@row, 10 + FIND("Quantity: ", [Column A]@row), FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row))  (10 + FIND("Quantity: ", [Column A]@row)))), 0)

Thank you so much, you sir are a LEGEND!
😁

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!