EXTRACT specific text into next cell

[makinmomb]

I have this on cell A1

NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT

I want to extract the word 60 x 60 into cell B1

 

[Rick Rothstein]

A small modification in my formula:

 =TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1))),SEARCH(" X ",SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1)))&" X ")-2*LEN(A1),4*LEN(A1)))

I am not sure I would call that a "small modification", but I like the resulting formula that you got from it. Nice!

I do not know where it came from, but just posting my response to Markmzz gave me an idea on how to reduce my formula considerably making it very short considering what it does (it uses only 7 total function calls)...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1,FIND(" X ",A1)+3)-1)," ",REPT(" ",99)),297))

Note: As set up, this formula will handle text strings of about 99 characters or less... if the text strings could be longer, let me know by how much and I'll modify it accordingly.

 

[XOR LX]

Very nice.

Can't top that but thought I'd chip in one more (also 7 function calls):

=TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",187)),FIND(" X ",SUBSTITUTE(A4," ",REPT(" ",187)))-200,394))

Regards

 

[markmzz]

I do not know where it came from, but just posting my response to Markmzz gave me an idea on how to reduce my formula considerably making it very short considering what it does (it uses only 7 total function calls)...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1,FIND(" X ",A1)+3)-1)," ",REPT(" ",99)),297))

Note: As set up, this formula will handle text strings of about 99 characters or less... if the text strings could be longer, let me know by how much and I'll modify it accordingly.

Hi Rick,

It's a Nice formula.

Maybe I'm wrong, but I found a problem (#VALUE!) with this data:

NIRO CEMENTUM 60 X 60

PS: I don't know if it's a possible data.

Markmzz

 

[Rick Rothstein]

Hi Rick,

It's a Nice formula.

Maybe I'm wrong, but I found a problem (#VALUE!) with this data:

NIRO CEMENTUM 60 X 60

PS: I don't know if it's a possible data.

I am guessing that it is not possible data based on the shape of the OP's example data; but, if it turns out it is, the fix is simple requiring just two extra characters shown in red)...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND(" X ",A1)+3)-1)," ",REPT(" ",99)),297))

 

[makinmomb]

I am guessing that it is not possible data based on the shape of the OP's example data; but, if it turns out it is, the fix is simple requiring just two extra characters shown in red)...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND(" X ",A1)+3)-1)," ",REPT(" ",99)),297))

That is phase one of the work completed , how then do I remain with text only removing 60 X 60 without FIND , RELACE ALL , formula required on cell C1 , since B1 has 60 X 60 extracted

NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT

NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY

<colgroup><col width="297" style="width: 223pt; mso-width-source: userset; mso-width-alt: 10861;"> <tbody> </tbody>

 

[Rick Rothstein]

That is phase one of the work completed , how then do I remain with text only removing 60 X 60 without FIND , RELACE ALL , formula required on cell C1 , since B1 has 60 X 60 extracted

NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT

NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY

<tbody>
</tbody>

This formula will give you the text with the "number X number" part..

=TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND(" X ",A1)+3)-1)," ",REPT(" ",99)),297)),""))

If you outputted the "number X number" part to another cell, then you can make the above formula more efficient by referencing that outputted text. Let's assume you outputted the "number X number" part to cell B1 then putting this formula in C1 would give you the same output as the above formula does....

=TRIM(SUBSTITUTE(A1,B1," "))

 

[markmzz]

That is phase one of the work completed , how then do I remain with text only removing 60 X 60 without FIND , RELACE ALL , formula required on cell C1 , since B1 has 60 X 60 extracted

NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT

NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY

<tbody>
</tbody>

You can try this too:

In B1

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1))),SEARCH(" X ",SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1)))&" X ")-2*LEN(A1),4*LEN(A1)))

In C1

=TRIM(SUBSTITUTE(A1,B1,""))

Markmzz

 

[makinmomb]

You can try this too:

In B1

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1))),SEARCH(" X ",SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1)))&" X ")-2*LEN(A1),4*LEN(A1)))

In C1

=TRIM(SUBSTITUTE(A1,B1,""))

Markmzz

Perfect I can now sort huge data by tile size into seconds not minutes anymore

I was just wondering if the simple MID formula can handle this too for cell b1 if rename the system codes to as follows, the mid formula though would need to handle to cases 4 ( NIRO ) characters and 5 (GEMMA ) characters

NIRO 60 X 60 CEMENTUM = GCM 01 WHITE MATT

GEMMA 30 X 30 = MONO COLOUR IVORY

<colgroup><col width="297" style="width: 223pt; mso-width-source: userset; mso-width-alt: 10861;"> <tbody> </tbody>

 

[makinmomb]

I have change NIRO CEMENTUM 60 x 60 to NIRO 60 x 60 CEMENTUM to see if MID formula can tackle the case

 

[abmati]

A1=

NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT

B1=MID(A1,SEARCH("60 X 60",A1,1),7)

<tbody>
</tbody>

Regards,

A.B. Mati