A small modification in my formula:
Code:=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1))),SEARCH(" X ",SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1)))&" X ")-2*LEN(A1),4*LEN(A1)))
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)...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.
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))
NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT |
NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY |
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>
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>
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,""))
You can try this too:
Code: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
NIRO 60 X 60 CEMENTUM = GCM 01 WHITE MATT |
GEMMA 30 X 30 = MONO COLOUR IVORY |