Help w Extracting numberic values from an array of text string

[Rachaelsqa]

Hello ,

I am working in a table . I have a column (Table1[SOC qty]) that needs to reflect the sum of the values in row to the right of it ( Table1[[#ThisRow],[column1:column50]]) . My desire is this formula will copy all the way down the column for all the rows in the table . Additionally like most formulas in a table , I would hope as columns are added ( none will be deleted) , the formula would accomodate.

The row contains alpha-numeric & numeric values in each column .
EXAMPLE
<TABLE style="WIDTH: 129pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=170 border=0><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" span=2 width=26><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 804" width=22><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><TBODY><TR style="HEIGHT: 66pt" height=88><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: black 0.5pt solid; WIDTH: 20pt; COLOR: #1f497d; BORDER-BOTTOM: #eae6dd 1pt; FONT-FAMILY: Calibri; HEIGHT: 66pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=26 height=88>column1</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: black 0.5pt solid; WIDTH: 20pt; COLOR: #1f497d; BORDER-BOTTOM: #eae6dd 1pt; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=26>column2</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: black 0.5pt solid; WIDTH: 26pt; COLOR: #1f497d; BORDER-BOTTOM: #eae6dd 1pt; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=35>column3</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: black 0.5pt solid; WIDTH: 17pt; COLOR: #1f497d; BORDER-BOTTOM: #eae6dd 1pt; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=22>column4</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: black 0.5pt solid; WIDTH: 20pt; COLOR: #1f497d; BORDER-BOTTOM: #eae6dd 1pt; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=26>column5</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: black 0.5pt solid; WIDTH: 26pt; COLOR: #1f497d; BORDER-BOTTOM: #eae6dd 1pt; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=35>column6</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #eae6dd; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: black 0.5pt solid; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" height=20>1</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #eae6dd; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: #eae6dd; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">q1</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #eae6dd; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: #eae6dd; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">q1x12</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #eae6dd; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: #eae6dd; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">q0</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #eae6dd; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: #eae6dd; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">5</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #eae6dd; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: #ffffcc; BORDER-LEFT: #eae6dd; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">q0x14</TD></TR></TBODY></TABLE>


What I desire to do :
I need to count the number of times q1 and q0 occur without the "x" and sum this value with numeric values in the test string after the X only.
I need the formula to ignore blanks as the # of column entries will vary , and I need the formula to ignore numbers.

For example using the entries from above , I would like the following result :
28. That is 1(q1)+1(q0)+12+14

I would prefer this be a formula (if possible) as opposed to a macro because the entries are not static and will constantly change. It would cause to much of a problem to constantly re-cal the sheet. Additionally the result value is used for real-time data , so to recal later on it not an option.

I was sucessful in finding a countif formula to count all the q1's & q0's .

I was also sucessful in finding a formula that would return the value of the numbers at the end of the text string after the "X" , however I could only make this work for 1 cell at a time and not in an array .

I've been at this for 8 hours and I am throwing in thr towel and calling in the pros!

Thank you so much!
~Rachael

 

[jbeaucaire]

1) Any chance you can post a sample table with more than 1 row of data? All the variations that have to be dealt with would be most helpful.

I use Excel Jeanie to post HTML because it includes columns and rows to make the visual clear. Does the tool you used do that?

2) Can you include in your sample example of the results expected, too? You indicate it's row by row then mention an array of the entire table...so i want to be sure your needs are fully understood.

 

[mikerickson]

If that data is in A1:F1, the CSE formula
=SUMPRODUCT(VALUE("0"&MID(A1:F1, FIND({"q1";"q0"}&"x", A1:F1&{"q1";"q0"}&"x")+LEN({"q1";"q0"})+1, 255))) + SUMPRODUCT(COUNTIF(A1:F1,{"q1";"q0"}))

will return 28.

This formula needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac))

 

[mikerickson]

This version extracts the q0,q1 from the range, rather than listing them explicitly
=SUM((CODE(TRANSPOSE(A1:F1))=CODE("q")) * VALUE(("0"& MID(A1:F1, FIND(TRANSPOSE(A1:F1)&"x",A1:F1&TRANSPOSE(A1:F1)&"x") + LEN(TRANSPOSE(A1:F1)&"x"), 255)))) + SUM((CODE(A1:F1)=CODE("q"))-ISNUMBER(FIND("x",A1:F1)))

 

[Rachaelsqa]

Dear Mikerickson you sir are a genius! Cant thank you enough!! Works like a charm .
I modifed the cell range and its absolutely perfect!

and thank you too jbeaicaire for replying to my post , it is much appreciated.


Take care and good night Pros!