Extracting numbers based on different text

[Spasmodic]

	R	S
1	SS	SS Notes
2	50 x 5	IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")
3	50's x 5	IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")

<tbody>
</tbody>

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]

	R	S
1	SS	SS Notes
2	50 x 5	IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")
3	50's x 5	IFERROR((LEFT(R3,SEARCH("'s x",[@[SS]])-1))/(1.0278-0.0278*REPLACE([@[SS]],1,FIND("x",[@[SS]]),"")),"")

<tbody>
</tbody>

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)))),"")