Formula to return characters after a letter and before the first space

[jimmyb555]

Hi everyone,

I am close to achieving my goal, I have two formulas in separate cells which give me the desired values, but I would like this all to be performed in one cell.

The first formula is in cell E2: =IF(ISNUMBER(SEARCH("x",A2)),RIGHT(A2,LEN(A2)-FIND("x",A2))," "). A2 contains a string of text such as: EE - Roof Sloped - SF x2 buildings.

The second one is in cell F2: =IFERROR(LEFT(E2,FIND(" ",E2)),E2)

I would like these two formulas to work within cell E2. The objective is to have a formula to search the string of text and return the number that follows the "x" and before the next space. All the help is much appreciated. I would like to avoid VBA, although I don't believe it is needed in this case.

Thank you!

[azumi]

ANd you need to combine formula for Cell E2? Am I right?

[jimmyb555]

Yes, I would like these two formulas combined in cell E2

[azumi]

Maybe:

=IFERROR(LEFT(E2,FIND(" ",E2)),E2)&" "&IF(ISNUMBER(SEARCH("x",E2)),RIGHT(E2,LEN(E2)-FIND("x",E2))," ")

[jimmyb555]

Unfortunately, this returned EE.

The idea is that in the text string "EE - Roof Sloped - SF x2 buildings", which is in cell A2, I want the formula (in cell E2) to return the number after the "x" but before the next space. In this case, 2.

Thank you for the suggestion tho.

[daddylonglegs]

How many digits might there be after the "x"? This will extract up to 3

=LOOKUP(1000,MID(E2,FIND("x",E2)+1,{1,2,3})+0)

[azumi]

=IFERROR(LEFT(IF(ISNUMBER(SEARCH("x",A2)),RIGHT(A2,LEN(A2)-FIND("x",A2))," "),FIND(" ",E2)),E2)

Sorry for my mistake

Just put your first formula:
IF(ISNUMBER(SEARCH("x",A2)),RIGHT(A2,LEN(A2)-FIND("x",A2))," ")

into part with red color:
=IFERROR(LEFT(E2,FIND(" ",E2)),E2)

and you'll find the number....