Extracting numbers based on different text

[Spasmodic]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SS[/TD]
[TD]SS Notes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]50 x 5[/TD]
[TD]IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]50's x 5[/TD]
[TD]IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")[/TD]
[/TR]
</tbody>[/TABLE]

Hello all,

I was having trouble extracting numbers based on certain text. For example the formula in S2 and S3 only work when I write 50's x 5 or 100's x 5. If I put in 40 x 5 or 50 x 5 (without the 's) the formula doesn't work and while I could manual change the formula to just x (without the 's) it would take too long for the hundreds of cells that I have to do and there are some cases where it is 50's x 5 and 50 x 5. Curious if there is anyway to have excel distinguish between the two in 1 formula? I would prefer not to use VBA if possible. Thank you

 

[jarjarbingie]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SS[/TD]
[TD]SS Notes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]50 x 5[/TD]
[TD]IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]50's x 5[/TD]
[TD]IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")[/TD]
[/TR]
</tbody>[/TABLE]

Hello all,

I was having trouble extracting numbers based on certain text. For example the formula in S2 and S3 only work when I write 50's x 5 or 100's x 5. If I put in 40 x 5 or 50 x 5 (without the 's) the formula doesn't work and while I could manual change the formula to just x (without the 's) it would take too long for the hundreds of cells that I have to do and there are some cases where it is 50's x 5 and 50 x 5. Curious if there is anyway to have excel distinguish between the two in 1 formula? I would prefer not to use VBA if possible. Thank you

S2=IFERROR(NUMBERVALUE(SUBSTITUTE(LEFT(R2,SEARCH(" x",R2)-1),"'s",""))/(1.0278-0.0278*NUMBERVALUE(RIGHT(R2,LEN(R2)-SEARCH("x",R2)))),"")