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]

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

I am not sure what problems you think might occur... the formulas Markmzz posted in Message #17 and the formulas I posted in Message #16 should be able to handle any number of leading characters.

 

[makinmomb]

I am not sure what problems you think might occur... the formulas Markmzz posted in Message #17 and the formulas I posted in Message #16 should be able to handle any number of leading characters.

I am fully sorted out , just wanted to know if MID formula can handle two character sets perhaps with an if combination

 

[makinmomb]

Plus down here its AFRICA , they won't handle seeing such a large formula ,
though as a stocker of the company I am done my end , its sort my case BEYOND FULLY

 

[Rick Rothstein]

I am fully sorted out , just wanted to know if MID formula can handle two character sets perhaps with an if combination

If you show us an exact example of the text you want to process, tell us where that text is (one cell, two cells) and then show us the output you want from the formula you apply to the text, someone here should be able to construct a formula that will do it.

 

[makinmomb]

If you show us an exact example of the text you want to process, tell us where that text is (one cell, two cells) and then show us the output you want from the formula you apply to the text, someone here should be able to construct a formula that will do it.

	RESULT	RESULT
ROMAN 30 X 60 = COSTA BEIGE = GL638011	30 X 60	ROMAN = COSTA BEIGE = GL638011
ROMAN 30 X 60 = COSTA BROWN = GL638012	30 X 60	ROMAN = COSTA BROWN = GL638012
NIRO 60 x 60 CEMNETUM = GCM 01 WHITE MATT	60 x 60	NIRO CEMNETUM = GCM 01 WHITE MATT
NIRO 60 X 60 REGAL = GMR 81 LIGHT GREY	60 X 60	NIRO REGAL = GMR 81 LIGHT GREY

<colgroup><col width="306" style="width: 230pt; mso-width-source: userset; mso-width-alt: 11190;"> <col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="260" style="width: 195pt; mso-width-source: userset; mso-width-alt: 9508;"> <tbody> </tbody>

 

[makinmomb]

results on B1 and C1 , A1 input cell

 

[makinmomb]

if formula with MID

 

[Rick Rothstein]

if formula with MID

I do not understand why you are asking for an IF formula with MID... it is not required. The formulas Markmzz posted in Message #17 do what you asked for in Message #25 directly. My formulas need minor tweaking to handle the lower case "x" you show in one of your examples (plus I added an additional change to make it not error out when A1 is blank)...

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

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

<tbody>
</tbody>

If I understand correctly what you want, maybe this can helps:

In B1

=TRIM(MID(A1,6,SEARCH("|",SUBSTITUTE(A1," ","|",4))-5))

Markmzz

 

[makinmomb]

If I understand correctly what you want, maybe this can helps:

In B1

=TRIM(MID(A1,6,SEARCH("|",SUBSTITUTE(A1," ","|",4))-5))

Markmzz

Yes I am happier with this shorter formula , thank you once again Markmzz