Divide cell at concatenate

[JeremySchubert]

Using XP and Excel 2007. I've made a multiplication test sheet for my kid. 100 questions, 4 rows by 4 columns, using the following formula.

=RANDBETWEEN(2,5)&" X "&RANDBETWEEN(2,12)&" = "

I would like to create a second worksheet that provides the answers. So do I need to figure out how to write a UDF that reads something like

1. Break the cell at each concatenate
2. Figure out which the two randomized numbers are
3. Show the product of those two numbers

Thanks, Jeremy

 

[markmzz]

Try this:

Questions
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>3 X 11 =</TD><TD>3 X 2 =</TD><TD>5 X 3 =</TD><TD>4 X 5 =</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>4 X 4 =</TD><TD>3 X 6 =</TD><TD>5 X 3 =</TD><TD>3 X 7 =</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>5 X 3 =</TD><TD>2 X 12 =</TD><TD>5 X 2 =</TD><TD>2 X 4 =</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>3 X 8 =</TD><TD>5 X 7 =</TD><TD>2 X 2 =</TD><TD>3 X 6 =</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(2,5) & " X " & RANDBETWEEN(1,12) & " ="</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Answers
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">33</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">21</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">35</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">18</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">=LEFT(Questions!A1,SEARCH(" X ",Questions!A1))*MID(Questions!A1,SEARCH(" =",Questions!A1)-2,2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[JeremySchubert]

Thanks Markmzz,

I understand the first part of your solution is basically saying

Search for number left of the " X "

Can you please explain the -2,2 part at the end of your formula? Is it saying each search should return a max of 2 characters?

Thanks, Jeremy

 

[markmzz]

Thanks Markmzz,

I understand the first part of your solution is basically saying

Search for number left of the " X "

Can you please explain the -2,2 part at the end of your formula? Is it saying each search should return a max of 2 characters?

Thanks, Jeremy

Yes 2 characters before space and =. It's ok for you?

Markmzz

 

[JeremySchubert]

Excellent! Thanks so much.
Jeremy

 

[markmzz]

Excellent! Thanks so much.
Jeremy

Thanks for the feedback.

Markmzz