Hi
I've got a formula which extracts the last two numbers in a cell and works correct if the cell says
2 for 23 but doesn't if the cell says
2for£23
Does anyone know how to fix this?
Please find below sample data which covers cells A1:B3.
The formula in cell B2 is =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
You can drag this down to cell B3 to get the result for B3. But the result in B3 should 23, like the result in B2.
Please let me know if you have any questions. TIA
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Mechanic[/TD]
[TD="class: xl64, width: 64"]Y[/TD]
[/TR]
[TR]
[TD]2 for 23[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]2for£23[/TD]
[TD]2for£23[/TD]
[/TR]
</tbody>[/TABLE]
I've got a formula which extracts the last two numbers in a cell and works correct if the cell says
2 for 23 but doesn't if the cell says
2for£23
Does anyone know how to fix this?
Please find below sample data which covers cells A1:B3.
The formula in cell B2 is =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
You can drag this down to cell B3 to get the result for B3. But the result in B3 should 23, like the result in B2.
Please let me know if you have any questions. TIA
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Mechanic[/TD]
[TD="class: xl64, width: 64"]Y[/TD]
[/TR]
[TR]
[TD]2 for 23[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]2for£23[/TD]
[TD]2for£23[/TD]
[/TR]
</tbody>[/TABLE]