Calculation using the last value in a cell

[bj6264]

Hello, can someone help please. I have a list of prices but some cells contain multiple values where the value has changed. I need a formula that will use the last value in a cell. Example below. As you can see the result column has now value as its using $3.41$3.117 in the calculation (I need it to use $3.117)

Price	Uplift	Calculation	Result
<S>$3.41</S> $3.117	0.8	=A2*B2	#VALUE!
<S>$5.80 </S> <S>$5.63</S> $4.634	1.3	=A3*B3	#VALUE!
$1.711	2.3	=A4*B4	3.9353

<COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=4 width=81><TBODY>
</TBODY>

	</SPAN>
<STRIKE></STRIKE></SPAN> </SPAN>	</SPAN>	</SPAN>	</SPAN>
</SPAN>	</SPAN>	</SPAN>	</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>

</SPAN>		</SPAN>
</SPAN> </SPAN>		</SPAN>
</SPAN> </SPAN>
</SPAN>	</SPAN>	</SPAN>	</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>

 

[buxtongt]

Will the price always be 4 digits like the examples you listed?

 

[bj6264]

Will the price always be 4 digits like the examples you listed?

Hello, unfortunately not or I could use the =right function.

 

[Rick Rothstein]

Hello, can someone help please. I have a list of prices but some cells contain multiple values where the value has changed. I need a formula that will use the last value in a cell. Example below. As you can see the result column has now value as its using $3.41$3.117 in the calculation (I need it to use $3.117)

Price	Uplift	Calculation	Result
<S>$3.41</S> $3.117	0.8	=A2*B2	#VALUE!
<S>$5.80 </S> <S>$5.63</S> $4.634	1.3	=A3*B3	#VALUE!
$1.711	2.3	=A4*B4	3.9353

<TBODY>
</TBODY>

I think this formula in C2 (copied down) should do what you want...

=TRIM(LEFT(RIGHT(SUBSTITUTE(CHAR(10)&A2,CHAR(10),REPT(" ",99)),99),99))*B2

 

[bj6264]

Stunning! Worked perfectly. Thank you!