Convert text to numbers in cell where text is extracted from another cel

[ajarle]

I am using this formula in B2 to extract the number "100" from A2 containing this text string: "viewport: 100x30".

=IFERROR(MID(A2,FIND(" ",A2)+1,FIND("x",A2)-FIND(" ",A2)-1))

All good so far.

However, I want the formula result to be formatted as a number. I am unable to change the cell format in the formula cell.

I am using Google sheets.

 

[gaz_chops]

Try
MID(A2,FIND(" ",A2)+1,FIND("x",A2)-FIND(" ",A2)-1))+0

 

[Rent23]

Using VALUE you convert text to number. So just add VALUE at the beginning of your formula.

 

[AlanY]

try this

=IFERROR(VALUE(MID(A2,FIND(" ",A2)+1,FIND("x",A2)-FIND(" ",A2)-1)),"")

you need to add this "" when it's an error

 

[gaz_chops]

The +0 at the end does the same.

 

[hiker95]

ajarle,

How about something like this?

Excel 2007
	A	B	C
2	viewport: 100x30	100	100
Sheet1

Cell Formulas
Range	Formula
C2	=IFERROR(VALUE(MID(A2,FIND(" ",A2)+1,FIND("x",A2)-FIND(" ",A2)-1)),"")
B2	{=MAX(IF(ISNUMBER(--MID(SUBSTITUTE(A2," ","x"),MOD(ROW(INDIRECT("1:"&LEN(A2)^2)),LEN(A2))+1,CEILING(ROW(INDIRECT("1:"&LEN(A2)^2))/LEN(A2),1))),--MID(A2,MOD(ROW(INDIRECT("1:"&LEN(A2)^2)),LEN(A2))+1,CEILING(ROW(INDIRECT("1:"&LEN(A2)^2))/LEN(A2),1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ajarle]

THank you so much to both of you!

 

[hiker95]

ajarle,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[gaz_chops]

You're welcome.

 

[gaz_chops]

Can I ask why this?
Doesn't this do the same?
=MID(A1,FIND(" ",A1)+1,FIND("x",A1)-FIND(" ",A1)-1)+0

ajarle,

How about something like this?

Array Formulas Cell Formula B2 {=MAX(IF(ISNUMBER(--MID(SUBSTITUTE(A2," ","x"),MOD(ROW(INDIRECT("1:"&LEN(A2)^2)),LEN(A2))+1,CEILING(ROW(INDIRECT("1:"&LEN(A2)^2))/LEN(A2),1))),--MID(A2,MOD(ROW(INDIRECT("1:"&LEN(A2)^2)),LEN(A2))+1,CEILING(ROW(INDIRECT("1:"&LEN(A2)^2))/LEN(A2),1))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>